mysql dba及l(fā)inux企業(yè)集群實(shí)戰(zhàn)工程師課程12-1觸發(fā)器

1、企業(yè)實(shí)戰(zhàn)DBA課程觸發(fā)器觸發(fā)器的作用自動(dòng)完成數(shù)據(jù)庫操作觸發(fā)器？觸發(fā)器是自動(dòng)化的SQL程序，由時(shí)間來觸發(fā)。、更新和刪除當(dāng)面對(duì)復(fù)雜的業(yè)務(wù)關(guān)系，在程序上難以實(shí)現(xiàn)時(shí)。當(dāng)程序上實(shí)現(xiàn)較為復(fù)雜，希望在數(shù)據(jù)庫內(nèi)實(shí)現(xiàn)時(shí)。例子1員工 1、us 2、sa表每增加一行則： er_total=old.user_total lary_total+new.salary+1user_totalsalary_total740000idnamesalary1Joe40002Ken50003Norman70004Tobb35005July60006Zen55007Bob9000觸發(fā)器的語法CREATE TRIGGER BEFORE

2、 | AFTER INSERT | UPDATE | DELETE ON FOR EACH ROW創(chuàng)建簡單觸發(fā)器BEFORE、AFTER和OLD、NEW一個(gè)簡單的例子1CREATE TRIGGER t1_aiAFTER INSERT ON t8 FOR EACH ROW UPDATE t2 SETuser_total=user_total+1, salary_total=salary_total+NEW.salary;一個(gè)簡單的例子2CREATE TRIGGER t1_adAFTER DELETE ON t8 FOR EACH ROW UPDATE t2 SETuser_total=user_

3、total-1, salary_total=salary_total-OLD.salary;一個(gè)簡單的例子3CREATE TRIGGER t1_auAFTER UPDATE ON t8 FOR EACH ROW UPDATE t2 SETsalary_total=salary_total-OLD.salary+NEW.salary;創(chuàng)建多執(zhí)行語句觸發(fā)器GEGIN和END。例子1員工 1、gi 2、gi表每增加一行則： d=0則user_total+1 d1則vip_total+1user_totalvip_total43idnamegid1Joe02Ken13Norman04Tobb05Jul

4、y16Zen17Bob0使用IF語句的觸發(fā)器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語句的觸發(fā)器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語句的觸發(fā)器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語句的觸發(fā)器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活動(dòng) 1、隨2、將3、循表每增加一行則：機(jī)挑選一名用戶信息 用戶信息獲獎(jiǎng)?wù)弑憝h(huán)指定次數(shù)idnamegidevent_date3Norman02014-1-1idnamegid1Joe02Ken13Norman04Tobb05July16Zen17Bob0使用LOOP語句的觸發(fā)器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語句的觸發(fā)器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語句的觸發(fā)器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語句的觸發(fā)器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語句的觸發(fā)器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語句的觸發(fā)器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;/查看觸發(fā)器SELE