Guest User

MySQL Triggers

a guest
Jun 19th, 2012
104
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP DATABASE IF EXISTS `test`;
  2. CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
  3. USE `test`;
  4.  
  5. #
  6. # TABLE STRUCTURE FOR TABLE post
  7. #
  8.  
  9. CREATE TABLE `post` (
  10.   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  11.   `text` VARCHAR(255) DEFAULT NULL,
  12.   `comments_num` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  13.   PRIMARY KEY (`id`)
  14. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
  15.  
  16. #
  17. # TABLE STRUCTURE FOR TABLE post_comments
  18. #
  19.  
  20. CREATE TABLE `post_comments` (
  21.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  22.   `comment` VARCHAR(255) DEFAULT NULL,
  23.   `post_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  24.   PRIMARY KEY (`id`),
  25.   KEY `post_comment` (`post_id`)
  26. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
  27.  
  28. #
  29. #  FOREIGN KEYS FOR TABLE post_comments
  30. #
  31.  
  32. ALTER TABLE `post_comments`
  33. ADD CONSTRAINT `post_comment` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`);
  34.  
  35. #
  36. #  Triggers FOR TABLE post_comments
  37. #
  38.  
  39. CREATE TRIGGER `comment_add` AFTER INSERT ON `post_comments`   
  40.     FOR EACH ROW BEGIN
  41.         SET @post_id = NEW.post_id;
  42.         SET @comments_num = (SELECT COUNT(id) FROM post_comments WHERE post_id = @post_id);
  43.         UPDATE `post` SET `comments_num` = @comments_num WHERE `id` = @post_id;
  44.     END;   
  45.    
  46. CREATE TRIGGER `comment_delete` AFTER DELETE ON `post_comments`
  47.     FOR EACH ROW BEGIN
  48.         SET @post_id = OLD.post_id;
  49.         SET @comments_num = (SELECT COUNT(id) FROM post_comments WHERE post_id = @post_id);
  50.         UPDATE `post` SET `comments_num` = @comments_num WHERE `id` = @post_id;
  51.     END;
RAW Paste Data