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

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

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

●

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

●

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

●

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

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

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

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

●

schema_name：可選項(xiàng)，指定觸發(fā)器所在數(shù)據(jù)庫(kù)名稱，如果沒(méi)有指定，則為當(dāng)前默認(rèn)數(shù)據(jù)庫(kù)。

●

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

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

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

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

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

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

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

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

數(shù)據(jù)庫(kù)原理與應(yīng)用（基于MySQL）1411.4事件的創(chuàng)建、修改和刪除11.4.1創(chuàng)建事件使用ALTEREVENT語(yǔ)句創(chuàng)建事件。語(yǔ)法格式：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ù)庫(kù)原理與應(yīng)用（基于MySQL）1511.4事件的創(chuàng)建、修改和刪除說(shuō)明：（1）event_name：指定事件名。（2）schedule：時(shí)間調(diào)度，表示事件何時(shí)發(fā)生或者每隔多久發(fā)生一次，有以下兩個(gè)子句：

●

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

●

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

數(shù)據(jù)庫(kù)原理與應(yīng)用（基于MySQL）1611.4事件的創(chuàng)建、修改和刪除【例11.6】創(chuàng)建現(xiàn)在立即執(zhí)行的事件E_direct，執(zhí)行時(shí)創(chuàng)建一個(gè)表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ù)庫(kù)原理與應(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í)行以下語(yǔ)句。mysql>SELECT*FROMtb;運(yùn)行結(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ù)庫(kù)原理與應(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()+