mysql触发器 

 1 /*
 2  * 添加数据之后报名人数加一
 3  */
 4 DELIMITER $$
 5 CREATE TRIGGER trigger_insert_enrolment_party_info
 6     AFTER INSERT ON `enrolment`
 7     FOR EACH ROW
 8     BEGIN
 9         UPDATE party_info SET signin_num=signin_num+1 WHERE id=new.party_id;
10     END$$
11     
12 /**
13  * 删除数据之后报名人数减一
14  */
15 DELIMITER $$
16 CREATE TRIGGER trigger_delete_enrolment_party_info
17     AFTER DELETE ON `enrolment`
18     FOR EACH ROW
19     BEGIN
20         UPDATE `party_info` SET signin_num = signin_num-1 WHERE id = old.party_id AND signin_num > 0;
21     END$$
22     
23 /**
24  * 修改报名状态：1、由待审核变成未通过(报名数量减一)，2、未通过变成待审核(报名数量加一)
25  */
26 DELIMITER $$
27 CREATE TRIGGER trigger_update_enrolment_party_info
28     AFTER UPDATE ON `enrolment`
29     FOR EACH ROW
30     BEGIN
31         IF old.status = 'unaudited' AND new.status = 'notpass'
32             THEN  SET @signin_num = signin_num-1;
33         ELSEIF (old.status = 'notpass' AND new.status = 'unaudited') OR (old.status = 'quit' AND new.status = 'unaudited')
34             THEN SET @signin_num = signin_num+1;
35         END IF;
36         UPDATE party_info SET signin_num = @signin_num WHERE id = new.party_id;
37     END$$

 

posted @ 2015-12-28 16:19  下一个雨季~  阅读(166)  评论(0)    收藏  举报