Triggers - Rescuers

Already many articles on the Internet have about sql triggers, but I will add one more with adequate examples to consolidate the material for those who are “in the subject line” and to better understand the material for those who have just begun to comprehend “Zen sql”. At the same time and create a discussion on the topic.

At once I will make a reservation that my opinion is only my opinion, it is sometimes very categorical. For a number of reasons, you have to work with high-load sites and complex web applications.


')
From work on them, they learned one valuable experience - to keep track of priorities and statistics. What does it mean? It's simple: if you have a blog and it has 2-3-4-10012 million visitors per day, and articles are written only 1-2-3-3435 times per day (an order of magnitude less than the number of views), then the speed of saving the article ( and the complexity of this) with respect to the speed of display of the article may be proportionally less. The more we show, the more critical it is to show, not to save the article / page / table. That does not mean that you can relax. Saving an article for 3-5-10 seconds in a blog is within the limits of adequacy, but page generation for a period of more than 2 seconds (+ while the scripts and styles with pictures are loaded) is on the verge of “what a decelerated site I read something else” , and even worse, "I'll go buy it in another place."

If we take the average site with voting / karma, comments, page display counter, etc., then many developers will immediately come to a head like SELECT count (*) FROM comment WHERE comment.page = page_id. Well, think about each article to count the amount of the rating, the amount of comments. And, we have on the main 10 articles from each section. With an attendance of 10 people per second, on an average VPS, you can afford 60-100 queries per sql per page (hello, bitrix).

But to hell lyrics (already got, probably). Bare data:

Table blog

CREATE TABLE IF NOT EXISTS `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `text` text NOT NULL,
  `creation` datetime NOT NULL,
  `modification` datetime NOT NULL,
  `img` varchar(128) NOT NULL DEFAULT 'default.png',
  `status` tinyint(4) NOT NULL DEFAULT '2',
  `user_id` int(11) NOT NULL,
  `rate` int(11) NOT NULL,
  `relax_type` tinyint(4) NOT NULL,
  `timers` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `contest` tinyint(1) NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '0',
  `comment` int(11) NOT NULL,
  `url` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`),
  KEY `country_id` (`country_id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;

comments

CREATE TABLE IF NOT EXISTS `comments` (
  `owner_name` varchar(50) NOT NULL,
  `owner_id` int(12) NOT NULL,
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `parent_id` int(12) DEFAULT NULL,
  `user_id` int(12) DEFAULT NULL,
  `text` text,
  `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `owner_name` (`owner_name`,`owner_id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;

, ( comment).
:
1. —
2. / — .
, — .

, 2 ( 3): ( — («», . .).
, .

: .

:

CREATE TRIGGER `add_count_comment` AFTER INSERT ON `comments`
 FOR EACH ROW BEGIN

 //          
UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id;

//            
CASE NEW.`owner_name`
WHEN 'Blog' THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `blog`.id = NEW.`owner_id` ;
WHEN 'Article' THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ;
WHEN 'PopulatePlace' THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ;
END CASE; 

//        
// url   ,       
CASE NEW.`owner_name`
WHEN 'Blog' THEN SET userurl = (SELECT url FROM `blog` WHERE `blog`.id= NEW.`owner_id`);
WHEN 'Article' THEN SET userurl = (SELECT url FROM `article` WHERE article.id=NEW.`owner_id`);
WHEN 'PopulatePlace' THEN SET userurl = ``;
END CASE;   

//    ,      
CASE NEW.`owner_name`
WHEN 'Blog' THEN SET usertitle = (select title from `blog` where blog.id=NEW.`owner_id`);
WHEN 'Article' THEN SET usertitle = (select title from `article` where article.id=NEW.`owner_id`);
WHEN 'PopulatePlace' THEN SET usertitle = ` `;
END CASE;   

INSERT INTO user_has_events VALUES (NEW.user_id,NEW.id,"Comments",NOW(),userurl , usertitle );

END

:

CREATE TRIGGER `del_count_comment` AFTER DELETE ON `comments`
 FOR EACH ROW BEGIN
UPDATE user SET user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id;

CASE OLD.`owner_name`
WHEN 'Blog' THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ;
WHEN 'Article' THEN  UPDATE `article` SET `article`.`comment` = `article`.`comment`-1 WHERE `article`.`id` = OLD.`owner_id` ;
WHEN 'PopulatePlace' THEN  UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ;
END CASE; 

END

, :
1. sql (, , )
2. ( . .)
3. .
4. .
5. ( 1 , «» .)

sphinx , . modification.

:

CREATE TRIGGER `ins_blog` BEFORE INSERT ON `blog` 
//        «» .
 FOR EACH ROW BEGIN 
SET NEW.modification = NOW();
END

.

:)

CREATE TRIGGER `ins_blog` BEFORE UPDATE ON `blog` 
//        «» .
 FOR EACH ROW BEGIN 
SET NEW.modification = NOW();
END

— .

sql — . sql , .

UPD: .