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$$
