第12章觸發(fā)器本章主要內(nèi)容創(chuàng)建觸發(fā)器刪除觸發(fā)器使用觸發(fā)器123主要內(nèi)容

觸發(fā)器(Tigger)的英文原譯為“扳機(jī)”或者是“觸發(fā)裝置”，而數(shù)據(jù)庫中的觸發(fā)器即意味著，以針對(duì)數(shù)據(jù)庫的操作(“觸發(fā)裝置”)而被調(diào)用的特殊存儲(chǔ)過程。MySQL語句在需要時(shí)被執(zhí)行，存儲(chǔ)過程也是如此。但是，如果你想要某條語句(或某些語句)在事件發(fā)生時(shí)自動(dòng)執(zhí)行，怎么辦呢？例如:a)每當(dāng)增加一位雇員到數(shù)據(jù)庫時(shí)，都檢查數(shù)據(jù)格式是否正確。b)每當(dāng)訂購(gòu)一個(gè)產(chǎn)品時(shí)，都從庫存數(shù)量中減去訂購(gòu)的數(shù)量。c)無論何時(shí)刪除一行，都在某個(gè)存檔表中保留一個(gè)副本。這些例子的共同之處是它們都需要在某個(gè)表發(fā)生更改時(shí)自動(dòng)處理。這確切地說就是觸發(fā)器。觸發(fā)器是MySQL響應(yīng)以下任意語句而自動(dòng)執(zhí)行的一條MySQL語句(或位于BEGIN和END語句之間的一組語句)：DELETE/INSERT/UPDATE。觸發(fā)器可以說是實(shí)現(xiàn)了針對(duì)相關(guān)表的處理自動(dòng)化的機(jī)制。定義觸發(fā)器使用CREATETRIGGER命令，具體語法如下createtrigger觸發(fā)器名[after/before]

insert/update/deleteon表名#與觸發(fā)器關(guān)聯(lián)的表名foreachrow#指定對(duì)于受觸事件影響的每一行都要激活觸發(fā)器的動(dòng)作beginsql語句：（觸發(fā)的語句一句或多句）end12.1創(chuàng)建觸發(fā)器

1.指定成為觸發(fā)器調(diào)用方的表名

觸發(fā)器并不是直接被調(diào)用運(yùn)行的，而是在針對(duì)具體表的操作時(shí)被調(diào)用。也就是說在創(chuàng)建觸發(fā)器時(shí)，需要指定針對(duì)哪個(gè)表的操作才能成為“觸發(fā)裝置”，而這個(gè)表就是觸發(fā)器調(diào)用方的表。12.1創(chuàng)建觸發(fā)器

2.決定觸發(fā)器運(yùn)行的時(shí)刻

觸發(fā)器的運(yùn)行時(shí)刻由[事件名]與[發(fā)生時(shí)刻]兩個(gè)因素決定。事件名就是啟動(dòng)觸發(fā)器的數(shù)據(jù)處理名，具體的就是指INSERT、UPDATE、DELETE等操作(但是，需要注意的是這些所謂數(shù)據(jù)操作命名并不是一定是嚴(yán)格意義上的INSERT、UPDATE、DELETE命令,例如INSERT事件并非必須運(yùn)行INSERT命令，像LOADDATAINFILE、REPLACE等伴隨有數(shù)據(jù)插入動(dòng)作的處理都屬于INSERT事件類)。發(fā)生時(shí)刻，就是指決定調(diào)用觸發(fā)器是在事件發(fā)生之前，還是事件發(fā)生之后。指定BEFORE或者AFTER兩個(gè)關(guān)鍵詞中的一個(gè)即可。12.1創(chuàng)建觸發(fā)器

例12.1在數(shù)據(jù)庫DB_SCHOOL的表tb_student中床架一個(gè)觸發(fā)器tb_student_insert_trigger,用于每次向表tb_student中插入一行數(shù)據(jù)時(shí)將學(xué)生變量str的值設(shè)置為‘onestudentadded!’Createtriggerdb.school.tb_student_insert_triggerafterinsertontb_studentForeachrowSet@str=‘onestudentadded!’Insertintodb_school.tb_studentvalues(‘2013110101’,’張曉勇’,’男’)；Select@str;使用DROPTRIGGER語句刪除觸發(fā)器，如下所示：

droptrigger[ifexists]觸發(fā)器名12.2刪除觸發(fā)器

觸發(fā)器不能更新或覆蓋。為了修改一個(gè)觸發(fā)器，必須先刪除它，然后再重新創(chuàng)建。例8.2刪除數(shù)據(jù)庫中的觸發(fā)器tb_student_insert_triggerDroptriggerifexistsdb_school.tb_student_isnert_trigger12.3使用觸發(fā)器12.3.1INSERT觸發(fā)器

創(chuàng)建一個(gè)觸發(fā)器。，當(dāng)向表emp插入數(shù)據(jù)時(shí)，通過觸發(fā)器將在另一張表logs表中插入日志信息。字段名數(shù)據(jù)類型說明idint(11)日志idlogvarchar(255)日志說明logs表創(chuàng)建語句:12.3使用觸發(fā)器12.3.1INSERT觸發(fā)器CREATETABLElogs(idint(11)PRIMARYKEYAUTO_INCREMENT,logvarchar(255)DEFAULTNULLCOMMENT'日志說明');【示例12-1】創(chuàng)建觸發(fā)器，向emp表插入信息12.3使用觸發(fā)器12.3.1INSERT觸發(fā)器DELIMITER$CREATETRIGGERtrg_emp_logAFTERINSERTONempFOREACHROWBEGINDECLAREs1VARCHAR(40)charactersetutf8;DECLAREs2VARCHAR(20)charactersetutf8;SETs2="iscreated";SETs1=CONCAT(NEW.ename,s2);INSERTINTOlogs(log)values(s1);END$DELIMITER;【示例12-1】執(zhí)行結(jié)果12.3使用觸發(fā)器12.3.1INSERT觸發(fā)器【示例12-2】向表emp中插入數(shù)據(jù)，觸發(fā)觸發(fā)器12.3使用觸發(fā)器12.3.1INSERT觸發(fā)器select*fromlogs;insertintoempvalues(7839,'King','president',null,'1981-11-17',5000,null,10);select*fromlogs;【示例12-2】執(zhí)行結(jié)果12.3使用觸發(fā)器12.3.1INSERT觸發(fā)器【示例12-3】創(chuàng)建觸發(fā)器，修改emp表信息，插入日志信息12.3使用觸發(fā)器12.3.2UPDATE觸發(fā)器DELIMITER$CREATETRIGGERtrg_emp_update_logAFTERUPDATEONempFOREACHROWBEGINDECLAREs1VARCHAR(40)charactersetutf8;DECLAREs2VARCHAR(20)charactersetutf8;SETs2="isupdate";SETs1=CONCAT(NEW.ename,s2);INSERTINTOlogs(log)values(s1);END$DELIMITER;【示例12-3】執(zhí)行結(jié)果12.3使用觸發(fā)器12.3.2UPDATE觸發(fā)器【示例12-4】修改表emp中的數(shù)據(jù)，查詢?nèi)罩拘畔?2.3使用觸發(fā)器12.3.2UPDATE觸發(fā)器updateempsetsal=6000whereempno=7839;select*fromlogs;【示例12-4】執(zhí)行結(jié)果12.3使用觸發(fā)器12.3.2UPDATE觸發(fā)器12.3使用觸發(fā)器12.3.3DELETE觸發(fā)器

刪除表emp的數(shù)據(jù)時(shí)，通過觸發(fā)器將被刪除的數(shù)據(jù)備份到另一張表emp_history。表emp_history信息如下：字段名數(shù)據(jù)類型說明idint(8)日志idempnoint(8)雇員編號(hào)enamevarchar(20)雇員名稱jobvarchar(9)職位mgrint領(lǐng)導(dǎo)編號(hào)hiredateint雇員入職日期字段名數(shù)據(jù)類型說明salint雇員的薪水commvarchar(10)雇員獎(jiǎng)金deptno int(2)雇員部門編號(hào)updateddatetime刪除日期12.3使用觸發(fā)器12.3.3DELETE觸發(fā)器emp_history表創(chuàng)建語句createtableemp_history(idint(8)primarykeyauto_increment,empnoint(4),ename varchar(10),job varchar(9),mgr int(4),hiredate date,sal decimal(7,2),comm decimal(7,2),deptno int(2),updateddatetime);12.3使用觸發(fā)器12.3.3DELETE觸發(fā)器【示例12-5】創(chuàng)建觸發(fā)器刪除emp表記錄，留下日志delimiter$createtriggertrg_emp_historyafterdeleteonempforeachrowbegininsertintoemp_historyvalues(default,old.empno,old.ename,old.job,old.mgr,old.hiredate,old.sal,m,old.deptno,now());end$delimiter;【示例12-5】執(zhí)行結(jié)果

在實(shí)際的數(shù)據(jù)庫應(yīng)用中，可能不僅僅要在刪除時(shí)留下數(shù)據(jù)的日志，例如，可能要在數(shù)據(jù)插入或者更新時(shí)留下原來數(shù)據(jù)的日志。可以清楚地看到，以上代碼有一點(diǎn)是觸發(fā)器特有的特征，那就是可以在觸發(fā)器定義中使用OLD/NEW關(guān)鍵字，具體可參照變更前后的數(shù)據(jù)記錄(如果發(fā)生時(shí)刻定義為BEFORE時(shí)，則使用[NEW.列名])。例如，在[OLD.empno]的情況下，表示從emp中刪除的記錄的雇員empno。12.3使用觸發(fā)器12.3.3DELETE觸發(fā)器對(duì)象事件的不同，使用的關(guān)鍵詞也不同，如下表觸發(fā)器類型NEW和OLD的使用INSERT觸發(fā)器NEW表示將要或已經(jīng)新增的數(shù)據(jù)UPDATE觸發(fā)器OLD表示將要或已經(jīng)被刪除的語句，NEW表示將要或已經(jīng)修改的數(shù)據(jù)DELETE觸發(fā)器 OLD表示將要或已經(jīng)被刪除的數(shù)據(jù)12.3使用觸發(fā)器12.3.3DELETE觸發(fā)器【示例12-6】表emp中的數(shù)據(jù)被刪除，觸發(fā)觸發(fā)器select*fromemp_history;deletefromempwhe