MySQL Triggers

DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;

#
# TABLE STRUCTURE FOR TABLE post
#

CREATE TABLE `post` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `text` VARCHAR(255) DEFAULT NULL,
  `comments_num` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

#
# TABLE STRUCTURE FOR TABLE post_comments
#

CREATE TABLE `post_comments` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `comment` VARCHAR(255) DEFAULT NULL,
  `post_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `post_comment` (`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

#
#  FOREIGN KEYS FOR TABLE post_comments
#

ALTER TABLE `post_comments`
ADD CONSTRAINT `post_comment` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`);

#
#  Triggers FOR TABLE post_comments
#

CREATE TRIGGER `comment_add` AFTER INSERT ON `post_comments`
    FOR EACH ROW BEGIN
        SET @post_id = NEW.post_id;
        SET @comments_num = (SELECT COUNT(id) FROM post_comments WHERE post_id = @post_id);
        UPDATE `post` SET `comments_num` = @comments_num WHERE `id` = @post_id;
    END;

CREATE TRIGGER `comment_delete` AFTER DELETE ON `post_comments`
    FOR EACH ROW BEGIN
        SET @post_id = OLD.post_id;
        SET @comments_num = (SELECT COUNT(id) FROM post_comments WHERE post_id = @post_id);
        UPDATE `post` SET `comments_num` = @comments_num WHERE `id` = @post_id;
    END;