北京林業(yè)大學(xué)《數(shù)據(jù)庫(kù)系統(tǒng)》第9講觸發(fā)器

9、Mysql觸發(fā)器本章內(nèi)容2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程29.1觸發(fā)器概述及使用方式概述9.2觸發(fā)器的創(chuàng)建和管理9.3Mysql事件9.1觸發(fā)器的定義9.1.1觸發(fā)器概述2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程3觸發(fā)器器是：一系列存儲(chǔ)在數(shù)據(jù)字典中的SQL代碼段；當(dāng)某些觸發(fā)條件，如插入、更新、修改滿足時(shí)，將被觸發(fā)；觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程，與存儲(chǔ)過(guò)程不同是的是觸發(fā)器是可以被自動(dòng)調(diào)用的，而存儲(chǔ)過(guò)程必須被手動(dòng)調(diào)用。9.1觸發(fā)器的定義9.1.2觸發(fā)器的優(yōu)勢(shì)2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程4觸發(fā)器的優(yōu)勢(shì)：觸發(fā)器可用于檢查數(shù)據(jù)的完整性。觸發(fā)器提供了數(shù)據(jù)庫(kù)層面的業(yè)務(wù)問(wèn)題發(fā)現(xiàn)機(jī)制。觸發(fā)器提供了執(zhí)行計(jì)劃任務(wù)的方法。觸發(fā)器可有效關(guān)注某些表格的數(shù)據(jù)變化。9.1觸發(fā)器的定義9.1.3觸發(fā)器的缺點(diǎn)2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程5觸發(fā)器的缺點(diǎn)：某些簡(jiǎn)單的驗(yàn)證需要在程序?qū)用嫱瓿?，不便于在?shù)據(jù)庫(kù)層面完成，如用戶輸入的基本信息。觸發(fā)器對(duì)于應(yīng)用程序是透明的。觸發(fā)器可能會(huì)提高數(shù)據(jù)庫(kù)服務(wù)器的復(fù)雜。9.1觸發(fā)器的使用9.1.4觸發(fā)器的事件類型2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程6由于觸發(fā)器被定義為在數(shù)據(jù)改變的事件下自動(dòng)執(zhí)行，所以根據(jù)改變數(shù)據(jù)的情況，觸發(fā)器的事件包括：BeforeInsert：插入數(shù)據(jù)前執(zhí)行觸發(fā)器。AfterInsert：插入數(shù)據(jù)后執(zhí)行觸發(fā)器。BeforeUpdate：更新數(shù)據(jù)前執(zhí)行觸發(fā)器。AfterUpdate：更新數(shù)據(jù)后執(zhí)行觸發(fā)器。BeforeDelete：刪除數(shù)據(jù)前執(zhí)行觸發(fā)器。AfterDelete：刪除數(shù)據(jù)后執(zhí)行觸發(fā)器。9.1觸發(fā)器的使用9.1.5觸發(fā)器的注意事項(xiàng)2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程7在Mysql5.7.2+版本上，可以對(duì)于一個(gè)事件，使用多個(gè)觸發(fā)器。除了Insert，Delete和Update關(guān)鍵字外，還有一些其他的關(guān)鍵字可能對(duì)數(shù)據(jù)進(jìn)行修改，如Truncate等，這些關(guān)鍵字并沒(méi)有對(duì)應(yīng)的觸發(fā)器。對(duì)于同一張表格，每個(gè)觸發(fā)器的名字是不同的，但是對(duì)于不同的表格，可以針對(duì)兩個(gè)表格的兩個(gè)觸發(fā)器使用相同的名字。9.1觸發(fā)器的使用9.1.6

觸發(fā)器的格式2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程8觸發(fā)器的命名規(guī)范如下也可以如下方式命名例子：before_order_update/order_before_update9.1觸發(fā)器的使用9.1.7mysql觸發(fā)器的限制2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程9Mysql觸發(fā)器不可用于的關(guān)鍵字：Show，Loaddata，loadtable，backupdatabase，restore，flush，return。事務(wù)的提交和回滾。動(dòng)態(tài)SQL語(yǔ)句中。9.2觸發(fā)器的創(chuàng)建9.2.1觸發(fā)器的創(chuàng)建2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程10觸發(fā)器的創(chuàng)建語(yǔ)法如下：Trigger_name：觸發(fā)器的名字。Trigger_time：觸發(fā)時(shí)間，Before|After。Trigger_event：觸發(fā)事件：Insert，Update，delete。Table_name：觸發(fā)器針對(duì)的表。9.2觸發(fā)器的創(chuàng)建9.2.2觸發(fā)器的創(chuàng)建的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程11有employee數(shù)據(jù)表如下：1.創(chuàng)建一張審計(jì)表，用于存儲(chǔ)employee的變化情況。9.2觸發(fā)器的創(chuàng)建9.2.2觸發(fā)器的創(chuàng)建的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程12建立該表的beforeupdate觸發(fā)器。注意New關(guān)鍵字和Old關(guān)鍵字的使用。Old表明更新前的數(shù)據(jù)。New表明更新后的數(shù)據(jù)。9.2觸發(fā)器的創(chuàng)建9.2.2觸發(fā)器的創(chuàng)建的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程13查看該表的beforeupdate觸發(fā)器。9.2觸發(fā)器的創(chuàng)建9.2.3多個(gè)觸發(fā)器的定義2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程14在MySql5.7.2之前，對(duì)于一個(gè)事件只能定義一個(gè)觸發(fā)器，在5.7.2+后，可以針對(duì)一個(gè)事件定義多個(gè)觸發(fā)器，多個(gè)觸發(fā)器按順序執(zhí)行。MySQL會(huì)按照創(chuàng)建的順序?qū)ν粋€(gè)事件執(zhí)行觸發(fā)器?？梢岳肍ollow關(guān)鍵字和Precedes關(guān)鍵字改變觸發(fā)器的執(zhí)行順序。9.2觸發(fā)器的創(chuàng)建9.2.4多個(gè)觸發(fā)器的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程15例子：假設(shè)每次改變產(chǎn)品價(jià)格的時(shí)候。我們希望將舊的價(jià)格記錄在獨(dú)立的一張日志表中。9.2觸發(fā)器的創(chuàng)建9.2.4多個(gè)觸發(fā)器的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程16創(chuàng)建一個(gè)觸發(fā)器，在update表格之前執(zhí)行。9.2觸發(fā)器的創(chuàng)建9.2.4多個(gè)觸發(fā)器的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程17修改產(chǎn)品價(jià)格。查看觸發(fā)器執(zhí)行情況9.2觸發(fā)器的創(chuàng)建9.2.4多個(gè)觸發(fā)器的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程18不僅需要查看修改的記錄，還要求查看是誰(shuí)修改了這個(gè)信息。創(chuàng)建新的日志表，記錄是誰(shuí)修改了價(jià)格信息。9.2觸發(fā)器的創(chuàng)建9.2.4多個(gè)觸發(fā)器的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程19創(chuàng)建第二個(gè)觸發(fā)器，當(dāng)?shù)谝粋€(gè)觸發(fā)器執(zhí)行完成后，執(zhí)行第二個(gè)觸發(fā)器，記錄誰(shuí)修改了這個(gè)表。9.2觸發(fā)器的創(chuàng)建9.2.4多個(gè)觸發(fā)器的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程20對(duì)第二個(gè)觸發(fā)器進(jìn)行測(cè)試。9.2觸發(fā)器的創(chuàng)建9.2.4多個(gè)觸發(fā)器的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程21查看觸發(fā)器的順序9.2觸發(fā)器的創(chuàng)建9.2.5刪除觸發(fā)器2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程22刪除觸發(fā)器的語(yǔ)法刪除觸發(fā)器的例子9.2觸發(fā)器的創(chuàng)建9.2.5刪除觸發(fā)器2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程23刪除觸發(fā)器的語(yǔ)法刪除觸發(fā)器的例子9.3MySQL事件9.3.1MySQL事件的定義2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程24MySQL事件是一組預(yù)定義的任務(wù)。MySQL事件通常也成為時(shí)態(tài)觸發(fā)器，由時(shí)間驅(qū)動(dòng)，而不是由事件驅(qū)動(dòng)。MySQL事件可以理解為Windows下的計(jì)劃任務(wù)。通過(guò)MySQL事件，可以進(jìn)行數(shù)據(jù)的優(yōu)化，日志的清理，數(shù)據(jù)的歸檔等。9.3MySQL事件9.3.2MySQL事件的執(zhí)行2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程25MySQL事件通過(guò)event_scheduler線程完成操作，默認(rèn)event_scheduler為關(guān)閉狀態(tài)，需要打開(kāi)后才可使用MySQL事件。查看打開(kāi)的情況。9.3MySQL事件9.3.3創(chuàng)建MySQL事件2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程26MySQL事件的創(chuàng)建與其他Mysql對(duì)象創(chuàng)建方式類似。Schedule為事件類型，主要分為一次型事件和遞歸執(zhí)行事件。一次型事件使用AT關(guān)鍵字，遞歸型事件使用EVERY關(guān)鍵字。9.3MySQL事件9.3.4MySQL事件的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程27使用事件向Message表填充數(shù)據(jù)創(chuàng)建一次型事件9.3MySQL事件9.3.4MySQL事件的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程28查看事件執(zhí)行結(jié)果查看數(shù)據(jù)庫(kù)的事件如果不在建立事件的時(shí)候設(shè)定，事件執(zhí)行完成后，事件就銷毀。9.3MySQL事件9.3.4MySQL事件的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程29為查看數(shù)據(jù)庫(kù)事件，建立一個(gè)延遲執(zhí)行的一次型事件查看數(shù)據(jù)庫(kù)的事件9.3MySQL事件9.3.4MySQL事件的例子2023/4/28數(shù)據(jù)庫(kù)原理及應(yīng)用教程30創(chuàng)建遞歸型事件注意st