MySQL-Trigger 使用案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DELIMITER @@
CREATE TRIGGER calculate_successive_failure_times
AFTER UPDATE
ON `sogou_weixin_article_monitor_task` FOR EACH ROW
BEGIN
-- 这里注意,DECLARE语句不能放在if中
DECLARE pre_sfr DECIMAL(10, 4) DEFAULT 0;
DECLARE current_sfr DECIMAL(10, 4) DEFAULT 0;
IF OLD.task_execute_failure_count > 0 THEN
SET pre_sfr = OLD.task_execute_count / OLD.task_execute_failure_count;
SET current_sfr = NEW.task_execute_count / NEW.task_execute_failure_count;
IF current_sfr < pre_sfr THEN
UPDATE `sogou_weixin_article_monitor_task` SET `successive_failure_times` = `successive_failure_times` + 1 WHERE id=NEW.id;
ELSE
UPDATE `sogou_weixin_article_monitor_task` SET `successive_failure_times` = 0 WHERE id=NEW.id;
END IF;
END IF;
END;@@
DELIMITER ;