第11章 觸發(fā)器和事件

1第11章觸發(fā)器和事件11.1觸發(fā)器概述11.2觸發(fā)器的創(chuàng)建、刪除和使用11.3事件概述11.4事件的創(chuàng)建、修改和刪除數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）211.1觸發(fā)器概述觸發(fā)器是一個被指定關(guān)聯(lián)到表的數(shù)據(jù)庫對象，與表的關(guān)系密切，它不需要用戶調(diào)用，而是在一個表的特定事件出現(xiàn)時將會被激活，此時某些MySQL語句會自動執(zhí)行。觸發(fā)器用于實現(xiàn)數(shù)據(jù)庫的完整性，觸發(fā)器具有以下特點：●可以提供更強大的約束?！窨蓪?shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改?！窨梢栽u估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施?！駨娭票淼男薷囊虾鯓I(yè)務(wù)規(guī)則。觸發(fā)器的缺點是增加決策和維護的復(fù)雜程度。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）311.2觸發(fā)器的創(chuàng)建、刪除和使用11.2.1創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器使用CREATETRIGGER語句。語法格式：CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtbl_nameFOREACHROWtrigger_body說明：（1）trigger_name：指定觸發(fā)器名稱。（2）trigger_time：觸發(fā)器被觸發(fā)的時刻，有兩個選項：BEFORE用于激活其語句之前觸發(fā)，AFTER用于激活其語句之后觸發(fā)。（3）trigger_event：觸發(fā)事件，有INSERT、UPDATE、DELETE。

●

INSERT：在表中插入新行時激活觸發(fā)器。

●

UPDATE：更新表中某一行時激活觸發(fā)器。

●

DELETE：刪除表中某一行時時激活觸發(fā)器。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）411.2觸發(fā)器的創(chuàng)建、刪除和使用（4）FOREACHROW：用于指定，對于受觸發(fā)事件影響的每一行，都要激活觸發(fā)器的動作。（5）trigger_body：觸發(fā)動作的主體，即觸發(fā)體，包含觸發(fā)器激活時將要執(zhí)行的語句。如果要執(zhí)行多個語句，可使用BEGIN...END復(fù)合語句結(jié)構(gòu)。綜上所述，可得創(chuàng)建觸發(fā)器的語法結(jié)構(gòu)包括觸發(fā)器定義和觸發(fā)體兩部分。觸發(fā)器定義包含指定觸發(fā)器名稱、指定觸發(fā)時間、指定觸發(fā)事件等。觸發(fā)體由MySQL語句塊組成，它是觸發(fā)器的執(zhí)行部分。在觸發(fā)器的創(chuàng)建中，每個表每個事件每次只允許一個觸發(fā)器，所以每條INSERT、UPDATE、DELETE的前或后可創(chuàng)建一個觸發(fā)器，每個表最多可創(chuàng)建6個觸發(fā)器。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）511.2觸發(fā)器的創(chuàng)建、刪除和使用【例11.1】在stusys數(shù)據(jù)庫的score表創(chuàng)建觸發(fā)器T_insertScoreRecord，當向score表插入一條記錄時，顯示“正在插入記錄”。創(chuàng)建觸發(fā)器。mysql>CREATETRIGGERT_insertScoreRecordAFTERINSERT->ONscoreFOREACHROWSET@str='正在插入記錄';QueryOK,0rowsaffected(0.11sec)驗證觸發(fā)器功能，向score表通過INSERT語句插入一條記錄。mysql>INSERTINTOscore->VALUES('196001','1004',91);QueryOK,1rowaffected(0.18sec)mysql>SELECT@str;運行結(jié)果：+---------------------+|@str|+---------------------+|正在插入記錄|+---------------------+1rowinset(0.00sec)

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）611.2觸發(fā)器的創(chuàng)建、刪除和使用11.2.2刪除觸發(fā)器刪除觸發(fā)器使用DROPTRIGGER語句。語法格式：DROPTRIGGER[schema_name]trigger_name說明：

●

schema_name：可選項，指定觸發(fā)器所在數(shù)據(jù)庫名稱，如果沒有指定，則為當前默認數(shù)據(jù)庫。

●

trigger_name：要刪除的數(shù)據(jù)庫名稱。當刪除一個表時，同時自動刪除該表上的觸發(fā)器?！纠?1.2】刪除觸發(fā)器T_insertScoreRecord。mysql>DROPTRIGGERT_insertScoreRecord;QueryOK,0rowsaffected(0.10sec)

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）711.2觸發(fā)器的創(chuàng)建、刪除和使用11.2.3使用觸發(fā)器1.INSERT觸發(fā)器INSERT觸發(fā)器在INSERT語句執(zhí)行之前或之后執(zhí)行。（1）INSERT觸發(fā)器的觸發(fā)體內(nèi)可引用一個名為NEW的虛擬表來訪問被插入的行。（2）在BEFOREINSERT觸發(fā)器中，NEW中的值可以被更新?！纠?1.3】在stusys數(shù)據(jù)庫的student表創(chuàng)建觸發(fā)器T_inserStudentRecord，當向student表插入一條記錄時，顯示插入記錄的學(xué)生的姓名。創(chuàng)建觸發(fā)器。mysql>CREATETRIGGERT_inserStudentRecordAFTERINSERT->ONstudentFOREACHROWSET@str1=NEW.sname;QueryOK,0rowsaffected(0.08sec)驗證觸發(fā)器功能，向student表通過INSERT語句插入一條記錄。mysql>INSERTINTOstudent->VALUES('196007','劉莉','女','1999-01-14','通信',50);QueryOK,1rowaffected(0.05sec)mysql>SELECT@str;

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）811.2觸發(fā)器的創(chuàng)建、刪除和使用運行結(jié)果：+----------+|@str|+----------+|劉莉|+----------+1rowinset(0.00sec)2.UPDATE觸發(fā)器UPDATE觸發(fā)器在UPDATE語句執(zhí)行之前或之后執(zhí)行。（1）INSERT觸發(fā)器的觸發(fā)體內(nèi)可引用一個名為OLD的虛擬表來訪問更新以前的值，也可引用一個名為NEW的虛擬表來訪問更新以后的值。（2）在BEFOREUPDATE觸發(fā)器中，NEW中的值可能已被更新。（3）OLD中的值不能被更新?！纠?1.4】在stusys數(shù)據(jù)庫的course表創(chuàng)建一個觸發(fā)器T_updateCourseScore，當更新表course中某門課程的課程號時，同時更新score表中所有相應(yīng)的課程號。創(chuàng)建觸發(fā)器。mysql>DELIMITER$$mysql>CREATETRIGGERT_updateCourseScoreAFTERUPDATE->ONcourseFOREACHROW

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）911.2觸發(fā)器的創(chuàng)建、刪除和使用->BEGIN->UPDATEscoreSETcno=NEW.cnoWHEREcno=OLD.cno;->END$$QueryOK,0rowsaffected(0.07sec)mysql>DELIMITER;驗證觸發(fā)器T_updateCourseScore的功能。mysql>UPDATEcourseSETcno='4017'WHEREcno='4002';QueryOK,1rowaffected(0.20sec)Rowsmatched:1Changed:1Warnings:0mysql>SELECT*FROMscoreWHEREcno='4017';運行結(jié)果：+----------+-------+--------+|sno|cno|grade|+----------+-------+--------+|196001|4017|90||196002|4017|79||196004|4017|88|+----------+-------+--------+3rowsinset(0.00sec)

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1011.2觸發(fā)器的創(chuàng)建、刪除和使用3.DELETE觸發(fā)器DELETE觸發(fā)器在DELETE語句執(zhí)行之前或之后執(zhí)行。（1）DELETE觸發(fā)器的觸發(fā)體內(nèi)可引用一個名為OLD的虛擬表來訪問被刪除的行。（2）OLD中的值不能被更新。【例11.5】在stusys數(shù)據(jù)庫的student表創(chuàng)建一個觸發(fā)器T_deleteStudentScore，當刪除表student中某個學(xué)生的記錄時，同時將score表中與該學(xué)生有關(guān)的數(shù)據(jù)全部刪除。創(chuàng)建觸發(fā)器。mysql>DELIMITER$$mysql>CREATETRIGGERT_deleteStudentScoreAFTERDELETE->ONstudentFOREACHROW

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1111.2觸發(fā)器的創(chuàng)建、刪除和使用->BEGIN->DELETEFROMscoreWHEREsno=OLD.sno;->END$$QueryOK,0rowsaffected(0.09sec)mysql>DELIMITER;驗證觸發(fā)器T_deleteStudentScore的功能。mysql>DELETEFROMstudentWHEREsno='191003';QueryOK,1rowaffected(0.09sec)mysql>SELECT*FROMscoreWHEREsno='191003';Emptyset(0.00sec)

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1211.3事件概述事件(Event)是在指定時刻才被執(zhí)行的過程式數(shù)據(jù)庫對象。事件通過MySQL中一個很有特色的功能模塊——事件調(diào)度器(EventScheduler)進行監(jiān)視，并確定其是否需要被調(diào)用。MySQL的事件調(diào)度器可以精確到每秒鐘執(zhí)行一個任務(wù)，比操作系統(tǒng)的計劃任務(wù)更具實時優(yōu)勢。對于一些實時性要求比較高的應(yīng)用，如股票交易、火車購票、球賽技術(shù)統(tǒng)計等就很適合。事件和觸發(fā)器相似，都是在某些事情發(fā)生時啟動，由于它們相似，所以事件又稱為臨時觸發(fā)器(TemporalTrigger)。它們的區(qū)別為：觸發(fā)器是基于某個表所產(chǎn)生的事件觸發(fā)的，而事件是基于特定的時間周期來觸發(fā)的。使用事件調(diào)度器之前，必須確保開啟事件調(diào)度器。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1311.3事件概述（1）查看當前是否開啟事件調(diào)度器SHOWVARIABLELIKE’EVENT_SCHEDULER’;或查看系統(tǒng)變量：SELECT@@EVENT_SCHEDULER;（2）如果沒有開啟事件調(diào)度器，可使用以下命令開啟SETGLOBLEEVENT_SCHEDULER=1;或SETGLOBLEEVENT_SCHEDULER=TRUE;或在MySQL的配置文件my.ini中加上”EVENT_SCHEDULER=1”或”SETGLOBLEEVENT_SCHEDULER=ON”，然后重啟MySQL服務(wù)器。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1411.4事件的創(chuàng)建、修改和刪除11.4.1創(chuàng)建事件使用ALTEREVENT語句創(chuàng)建事件。語法格式：CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULEschedule[ENABLE|DISABLE|DISABLEONSLAVE]DOevent_body;其中，scheduled的描述為：ATtimestamp[+INTERVALinterval]...|EVERYinterval[STARTStimestamp[+INTERVALinterval]...][ENDStimestamp[+INTERVALinterval]...]interval的描述為：quantity|YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND|

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1511.4事件的創(chuàng)建、修改和刪除說明：（1）event_name：指定事件名。（2）schedule：時間調(diào)度，表示事件何時發(fā)生或者每隔多久發(fā)生一次，有以下兩個子句：

●

ATtimestamp子句：指定事件在某個時刻發(fā)生。其中，timestamp為一個具體時間點，后面可加上時間間隔；interval為時間間隔，由數(shù)值和單位組成；quantity是時間間隔的數(shù)值。

●

EVERYinterval子句：表示事件在指定時間區(qū)間內(nèi)，每隔多久發(fā)生一次。其中，STARTS子句指定開始時間，ENDS子句指定結(jié)束時間，（3）event_body：DO子句中的event_body，用于指定事件啟動時要求執(zhí)行的代碼。如果要執(zhí)行多個語句，可使用BEGIN...END復(fù)合語句結(jié)構(gòu)。（4）ENABLE|DISABLE|DISABLEONSLAVE：可選項，表示事件的屬性。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1611.4事件的創(chuàng)建、修改和刪除【例11.6】創(chuàng)建現(xiàn)在立即執(zhí)行的事件E_direct，執(zhí)行時創(chuàng)建一個表tb。mysql>CREATEEVENTE_direct->ONSCHEDULEATNOW()->DO->CREATETABLEtb(timelinetimestamp);QueryOK,0rowsaffected(0.10sec)mysql>SHOWTABLES;+----------------------+|Tables_in_stusys|+----------------------+|course||lecture||score||student||tb||teacher|+---------------------+6rowsinset(0.00secmysql>SELECT*FROMtb;Emptyset(0.00sec)

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1711.4事件的創(chuàng)建、修改和刪除【例11.7】創(chuàng)建事件E_insertTb，每2秒鐘插入一條記錄到表tb。mysql>CREATEEVENTE_insertTb->ONSCHEDULEEVERY2SECOND->DO->INSERTINTOtbVALUES(current_timestamp);QueryOK,0rowsaffected(0.02sec)12秒鐘后執(zhí)行以下語句。mysql>SELECT*FROMtb;運行結(jié)果：+--------------------------+|timeline|+--------------------------+|2021-02-0215:10:23||2021-02-0215:10:25||2021-02-0215:10:27||2021-02-0215:10:30||2021-02-0215:10:31||2021-02-0215:10:33|+--------------------------+6rowsinset(0.00sec)

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1811.4事件的創(chuàng)建、修改和刪除【例11.8】創(chuàng)建事件E_startDays，從第2天起，每天清空表tb，在2021年12月31日結(jié)束。mysql>DELIMITER$$mysql>CREATEEVENTE_startDays->ONSCHEDULEEVERY1DAY->STARTSCURDATE()+