mysql dba及linux企业集群实战工程师课程12-1触发器

1、企业实战DBA课程触发器触发器的作用自动完成数据库操作触发器？触发器是自动化的SQL程序，由时间来触发。、更新和删除当面对复杂的业务关系，在程序上难以实现时。当程序上实现较为复杂，希望在数据库内实现时。例子1员工 1、us 2、sa表每增加一行则： er_total=old.user_total lary_total+new.salary+1user_totalsalary_total740000idnamesalary1Joe40002Ken50003Norman70004Tobb35005July60006Zen55007Bob9000触发器的语法CREATE TRIGGER BEFORE

2、 | AFTER INSERT | UPDATE | DELETE ON FOR EACH ROW创建简单触发器BEFORE、AFTER和OLD、NEW一个简单的例子1CREATE TRIGGER t1_aiAFTER INSERT ON t8 FOR EACH ROW UPDATE t2 SETuser_total=user_total+1, salary_total=salary_total+NEW.salary;一个简单的例子2CREATE TRIGGER t1_adAFTER DELETE ON t8 FOR EACH ROW UPDATE t2 SETuser_total=user_

3、total-1, salary_total=salary_total-OLD.salary;一个简单的例子3CREATE TRIGGER t1_auAFTER UPDATE ON t8 FOR EACH ROW UPDATE t2 SETsalary_total=salary_total-OLD.salary+NEW.salary;创建多执行语句触发器GEGIN和END。例子1员工 1、gi 2、gi表每增加一行则： d=0则user_total+1 d1则vip_total+1user_totalvip_total43idnamegid1Joe02Ken13Norman04Tobb05Jul

4、y16Zen17Bob0使用IF语句的触发器1DELIMITER /CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROWIF NEW.gid = 0 THENUPDATE t4 SET user_total=user_total+1; ELSE UPDATE t4 SET vip_total=vip_total+1; END IF;/使用IF语句的触发器2DELIMITER /CREATE TRIGGER t3_ad AFTER DELETE ON t3 FOR EACH ROWIF OLD.gid = 0 THENUPDATE t4 SET

5、 user_total=user_total-1; ELSE UPDATE t4 SET vip_total=vip_total-1; END IF;/使用IF语句的触发器3DELIMITER /CREATE TRIGGER t3_au AFTER UPDATE ON t3 FOR EACH ROWIF NEW.gid!=OLD.gid and NEW.gid=0 THENUPDATE t4 SET user_total=user_totoal+1,vip_total=vip_total-1;ELSEIF NEW.gid!=OLD.gid and NEW.gid=1 THENUPDATE t4

6、 SET user_totoal=user_total-1,vip_total=vip_total+1;END IF；/使用CASE语句的触发器1DELIMITER /CREATE TRIGGER t3_au AFTER UPDATE ON t3 FOR EACH ROWCASEWHEN NEW.gid!=OLD.gid AND NEW.gid=0 THENUPDATE t4 SET user_total=user_total+1,vip_total=vip_total-1; WHEN NEW.gid!=OLD.gid AND NEW.gid=1 THENUPDATE t4 SET user_

7、total=user_total-1,vip_total=vip_total+1;END CASE;/例子1event_datewinners_nu m2014-1-13活动 1、随2、将3、循表每增加一行则：机挑选一名用户信息 用户信息获奖者表环指定次数idnamegidevent_date3Norman02014-1-1idnamegid1Joe02Ken13Norman04Tobb05July16Zen17Bob0使用LOOP语句的触发器1CREATE TRIGGER t5_aiAFTER INSERT ON t5 FOR EACH ROW BEGINDECLARE mnumDEFAUL

8、T 0;DECLARE wnum; DECLARE tdate DATE;SET tdate=NEW.event_date;SET wnum=NEW.wnnum;使用LOOP语句的触发器2t5_loop:LOOPSET mnum=mnum+1;INSERTO t6 SELECT id,name,gid,tdate FROM t3 ORDER BY rand() limit 1;IF mnum=wnum THEN LEAVE t5_loop; END IF;END LOOP t5_loop;END;/使用LOOP语句的触发器3SET wnum=NEW.wnnum;CREATE TRIGGER t

9、5_aiAFTER INSERT ON t5 FOR EACH ROW BEGINDECLARE mnumDEFAULT 0;DECLARE wnum ; DECLARE tnum ; DECLARE tdate DATE;SELECT count(*)o tnum from t3; SET tdate=NEW.event_date;使用LOOP语句的触发器4/t5_loop:LOOPSET mnum=mnum+1;SET rnum=floor(1+rand()*tnum);INSERTO t6 SELECT id,name,gid,tdateFROM t3 WHERE id=rnum;IF

10、mnum=wnum THEN LEAVE t5_loop; END IF;END LOOP t5_loop; END;使用REPEAT语句的触发器1REPEATSET mnum=mnum+1;SET rnum=floor(1+rand()*tnum);INSERTO t6 SELECT id,name,gid,tdateFROM t3 WHERE id=rnum; UNTIL mnum=wnum END REPEAT;END;/使用WHILE语句的触发器1WHILE mnumwnumSET mnum=mnum+1;SET rnum=floor(1+rand()*tnum);INSERTO t6 SELECT id,name,gid,tdateFROM t3 WHERE id=rnum;END WHILE;END;/查看触发器SELE