MySQL進階三板斧（三）看清“觸發器 (Trigger)”的真實面目

觸發器（Trigger）的起源

MySQL是最受歡迎的開源RDBMS，被社群和企業廣泛使用。觸發器是MySQL在5。0。1（開天闢地一版本）中增加的三大新功能之一，另外兩個師兄弟是檢視（view）與儲存過程（procedure）。均屬於相對“高階”一點的資料庫必需功能。

一、什麼是觸發器

觸發器（trigger）：監視某種情況，並觸發執行某種操作。觸發器是在表中資料發生更改時自動觸發執行的，它是與表事件相關的特殊的儲存過程，它的執行不是由程式呼叫，也不是手工啟動，而是由事件來觸發，例如當對一個表進行操作（insert，delete， update）時就會啟用它執行。也就是說觸發器只執行DML事件（insert、update和delete）

MySQL觸發器的作用

安全性。可以基於資料庫的值使使用者具有操作資料庫的某種權利。

可以基於時間限制使用者的操作，例如不允許下班後和節假日修改資料庫資料。

可以基於資料庫中的資料限制使用者的操作，例如不允許股票的價格的升幅一次超過10%。

審計。可以

跟蹤使用者對資料庫的操作

。

審計使用者操作資料庫的語句。

把使用者對資料庫的更新寫入審計表。

實現複雜的資料完整性規則

實現非標準的資料完整性檢查和約束。觸發器可產生比規則更為複雜的限制。與規則不同，觸發器可以引用列或資料庫物件。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨。

提供可變的預設值。

實現複雜的非標準的資料庫相關完整性規則。

觸發器可以對資料庫中相關的表進行

連環更新

。

例如，在auths表author_code列上的刪除觸發器可導致相應刪除在其它表中的與之匹配的行。

在修改或刪除時級聯修改或刪除其它表中的與之匹配的行。

在修改或刪除時把其它表中的與之匹配的行設成NULL值。

在修改或刪除時把其它表中的與之匹配的行級聯設成預設值。

觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行資料更新的事務。當插入一個與其主健不匹配的外部鍵時，這種觸發器會起作用。例如，可以在books。author_code 列上生成一個插入觸發器，如果新值與auths。author_code列中的某值不匹配時，插入被回退。

同步實時地複製表中的資料。

自動計算資料值，如果資料的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低於5萬元則立即給財務人員傳送警告資料。

二、觸發器語法

1。建立語法四要素

監視地點(table)

監視事件(insert | update | delete)

觸發時間(after | before)

觸發事件(insert | update | delete)

2。語法公式

CREATE TRIGGER BEFORE|AFTER INSERT|UPDATE|DELETE ON # 表名 FOR EACH ROW # 這句話在mysql是固定的 BEGIN <觸發的SQL語句>（呼叫NEW/OLD引數）； END

3。語法引數說明

CREATE TRIGGER ——- 觸發器必須有名字，最多64個字元，可能後面會附有分隔符。它和MySQL中其他物件的命名方式基本相象。

{ BEFORE | AFTER } ——- 觸發器觸發時間設定：可以設定為事件發生前或後（前：一般用於校驗；後：一般用於關聯）。

{ INSERT | UPDATE | DELETE } —— 設定觸發事件：如執行insert、update或delete的過程時啟用觸發器。

ON ——- 觸發器是屬於某一個表的：當在這個表上執行 INSERT|UPDATE|DELETE 操作的時候就導致觸發器的啟用。同時，我們不能給同一張表的“同一個事件”安排兩個觸發器（意味著不能同時有兩個Insert觸發器）。

FOR EACH ROW ——- 觸發器的執行間隔（必有的公式內容）：FOR EACH ROW子句通知觸發器每隔一行執行一次動作，而不是對整個表執行一次。

<觸發的SQL語句> ——- 觸發器包含所要觸發的SQL語句：這裡的語句可以是任何合法的語句，包括複合語句，但是這裡的語句受的限制和函式的一樣。當然，觸發SQL中可以呼叫“觸發了（ INSERT | UPDATE | DELETE ）觸發器的那一行資料”。

例如下方程式碼

create trigger add_stuafter insert on student for each row begin insert into student_score （ stu_id， score， rank） values（ NEW。stuid， NEW。username）； —— NEW用來表示將要（BEFORE）或已經（AFTER）插入的新資料end；

4。 NEW 與 OLD關鍵字詳解

MySQL 中定義了 NEW 和 OLD，用來表示觸發器的所在表中，觸發了觸發器的那一行資料，來引用觸發器中發生變化的記錄內容，具體地：

① 在INSERT型觸發器中，NEW用來表示將要（BEFORE）或已經（AFTER）插入的新資料；

② 在UPDATE型觸發器中，OLD用來表示將要或已經被修改的原資料，NEW用來表示將要或已經修改為的新資料；

③ 在DELETE型觸發器中，OLD用來表示將要或已經被刪除的原資料；

另外，原則上請編寫簡單高效的觸發執行語句，以免悄無聲息的浪費過多資源你還不知道！

三、實際應用

1。資料準備

想我初三時常年倒數，成績穩定，因此我拿當時幾位老友排名資料來紀念一波（手動撓頭）。給大家提供一個測試資料；（沿用的前兩篇“檢視”、“儲存過程”博文中的資料）

a.學生表

CREATE TABLE `student` （ `ID` int NOT NULL AUTO_INCREMENT ， `NAME` varchar（30） NOT NULL ， `SEX` char（2） NOT NULL ， `AGE` int NOT NULL ， `CLASS` varchar（10） NOT NULL ， `GRADE` varchar（20） NOT NULL ， `HOBBY` varchar（100） NULL ， PRIMARY KEY （`ID`））

#插入資料：

INSERT INTO `student` （`ID`， `NAME`， `SEX`， `AGE`， `CLASS`， `GRADE`， `HOBBY`） VALUES （‘1’， ‘陳哈哈’， ‘男’， ‘15’， ‘18班’， ‘9年級’， ‘上網’）；INSERT INTO `student` （`ID`， `NAME`， `SEX`， `AGE`， `CLASS`， `GRADE`， `HOBBY`） VALUES （‘2’， ‘扈亞鵬’， ‘男’， ‘15’， ‘18班’， ‘9年級’， ‘美食’）；INSERT INTO `student` （`ID`， `NAME`， `SEX`， `AGE`， `CLASS`， `GRADE`， `HOBBY`） VALUES （‘3’， ‘劉曉莉’， ‘女’， ‘14’， ‘18班’， ‘9年級’， ‘金希澈’）；INSERT INTO `student` （`ID`， `NAME`， `SEX`， `AGE`， `CLASS`， `GRADE`， `HOBBY`） VALUES （‘4’， ‘朱志鵬’， ‘男’， ‘15’， ‘18班’， ‘9年級’， ‘睡覺’）；INSERT INTO `student` （`ID`， `NAME`， `SEX`， `AGE`， `CLASS`， `GRADE`， `HOBBY`） VALUES （‘5’， ‘徐立楠’， ‘女’， ‘14’， ‘18班’， ‘9年級’， ‘閱讀’）；INSERT INTO `student` （`ID`， `NAME`， `SEX`， `AGE`， `CLASS`， `GRADE`， `HOBBY`） VALUES （‘6’， ‘顧昊’， ‘男’， ‘15’， ‘5班’， ‘9年級’， ‘籃球’）；INSERT INTO `student` （`ID`， `NAME`， `SEX`， `AGE`， `CLASS`， `GRADE`， `HOBBY`） VALUES （‘7’， ‘陳子凝’， ‘女’， ‘15’， ‘18班’， ‘9年級’， ‘看電影’）；

#插入結果：

b.成績表

CREATE TABLE `student_score` （ `SID` int（11） NOT NULL， `S_NAME` varchar（30） NOT NULL， `TOTAL_SCORE` int（11） NOT NULL， `RANK` int（11） NOT NULL， PRIMARY KEY （`SID`））

#插入資料：

INSERT INTO `student_score` （`SID`， `S_NAME`， `TOTAL_SCORE`， `RANK`） VALUES （‘1’， ‘陳哈哈’， ‘405’， ‘1760’）；INSERT INTO `student_score` （`SID`， `S_NAME`， `TOTAL_SCORE`， `RANK`） VALUES （‘2’， ‘扈亞鵬’， ‘497’， ‘1000’）；INSERT INTO `student_score` （`SID`， `S_NAME`， `TOTAL_SCORE`， `RANK`） VALUES （‘3’， ‘劉曉莉’， ‘488’， ‘1170’）；INSERT INTO `student_score` （`SID`， `S_NAME`， `TOTAL_SCORE`， `RANK`） VALUES （‘4’， ‘朱志鵬’， ‘405’， ‘1770’）；INSERT INTO `student_score` （`SID`， `S_NAME`， `TOTAL_SCORE`， `RANK`） VALUES （‘5’， ‘徐立楠’， ‘530’， ‘701’）；INSERT INTO `student_score` （`SID`， `S_NAME`， `TOTAL_SCORE`， `RANK`） VALUES （‘6’， ‘顧昊’， ‘485’， ‘1286’）；INSERT INTO `student_score` （`SID`， `S_NAME`， `TOTAL_SCORE`， `RANK`） VALUES （‘7’， ‘陳子凝’， ‘704’， ‘9’）；

#插入結果：

c.逃課上網表

CREATE TABLE `student_go_wangba` （ `SID` int（9） NOT NULL， `SGW_NAME` varchar（30） DEFAULT NULL， `TIMES` int（9） DEFAULT NULL， PRIMARY KEY （`SID`））

#插入資料：

INSERT INTO `student_go_wangba` （`SID`， `SGW_NAME`， `TIMES`） VALUES （‘1’， ‘陳哈哈’， 15）；INSERT INTO `student_go_wangba` （`SID`， `SGW_NAME`， `TIMES`） VALUES （‘2’， ‘扈亞鵬’， 1）；INSERT INTO `student_go_wangba` （`SID`， `SGW_NAME`， `TIMES`） VALUES （‘3’， ‘劉曉莉’， 0）；INSERT INTO `student_go_wangba` （`SID`， `SGW_NAME`， `TIMES`） VALUES （‘4’， ‘朱志鵬’， 63）；INSERT INTO `student_go_wangba` （`SID`， `SGW_NAME`， `TIMES`） VALUES （‘5’， ‘徐立楠’， 0）；INSERT INTO `student_go_wangba` （`SID`， `SGW_NAME`， `TIMES`） VALUES （‘6’， ‘顧昊’， 7）；INSERT INTO `student_go_wangba` （`SID`， `SGW_NAME`， `TIMES`） VALUES （‘7’， ‘陳子凝’， 0）；

#插入結果：

2。使用案例

# Insert觸發器 - 級聯插入

表資料：以上面的三張表為例；學生表（student）、學生成績表（student_score）、逃課上網次數表（student_go_wangba），均已學號（stuid）為主鍵。

需求：

需要設計一個觸發器A，當增加新的學生時，需要在成績表（student_score）中插入對應的學生資訊，至於“分值、排名”欄位為0即可；後面由老師打分更新。

需要設計一個觸發器B，當增加新的學生成績資訊時，需要在逃課上網表（student_go_wangba）中插入對應的學生資訊，至於“逃課上網次數”欄位為0即可；後面由教導主任“小平頭”去更新。（該觸發器意義在於：測試after insert鏈式反應是否支援）

那麼，如何設計觸發器A呢？

首先它是一個插入Insert觸發器，是建立在表student上的；

然後是after，插入後的事件；

事件內容是插入成績表，需要插入學生的學號和姓名，number為自增，而“分值、排名”目前不需要。

注意：

new表示student中新插入的值

觸發器A:

—— 新增觸發器A，當student表插入資料時，student_score表生成初始關聯資料DROP TRIGGER IF EXISTS add_stu；create trigger add_stuafter insert on student for each row begin INSERT INTO student_score （SID， S_NAME， TOTAL_SCORE， RANK） VALUES （new。ID，new。NAME， 0， 0 ）；end；

觸發器B:

—— 新增觸發器B，當student_score表插入資料時，student_go_wangba表生成初始關聯資料DROP TRIGGER IF EXISTS add_score；create trigger add_scoreafter insert on student_score for each row begin INSERT INTO student_go_wangba （SID， SGW_NAME， TIMES） VALUES （new。SID，new。S_NAME， 0 ）；end；

查詢一下我的觸發器：

show triggers \G —— “\G”是幹什麼用的？ —— 作用：在shell中樹形展示

如果在Navicat中就不用 \G，直接“show triggers；”就可以。

show triggers；

執行觸發器，向student表中加入一條資料：

INSERT INTO `student` （`ID`， `NAME`， `SEX`， `AGE`， `CLASS`， `GRADE`， `HOBBY`） VALUES （‘8’， ‘李昂’， ‘男’， ‘15’， ‘18班’， ‘9年級’， ‘看片兒’）；

結果如下圖所示：

同時插入三個資料，兩個觸發器正確執行了~

注意：建立觸發器和表一樣，建議增加判斷：

DROP TRIGGER IF EXISTS `add_stu`;

# Delete觸發器 - 級聯刪除

表資料：以上面的三張表為例；學生表（student）、學生成績表（student_score）、逃課上網次數表（student_go_wangba），均已學號（stuid）為主鍵。

需求：有些老是逃課上網的學生被開除，需要刪掉所有資訊，以免給學校抹黑~~~

需要設計一個觸發器C，當刪除新的學生時，需要在成績表（student_score）中刪除對應的學生資訊。

需要設計一個觸發器D，當刪除新的學生成績資訊時，需要在逃課上網表（student_go_wangba）中刪除對應的學生資訊。（該觸發器意義在於：測試after delete鏈式反應是否支援）

那麼，如何設計觸發器C呢？

首先它是一個插入delete觸發器，是建立在表student上的；

然後是after，插入後的事件；

事件內容是關聯刪除成績表資料，需要刪除學生的學號即可。

注意：

old表示student中新刪除的值

。

觸發器C:

—— 新增觸發器C，當student表刪除資料時，student_score表刪除關聯資料DROP TRIGGER IF EXISTS del_stu；create trigger del_stuafter delete on student for each row begin DELETE FROM student_score where SID = old。ID；end；

觸發器D:

—— 新增觸發器D，當student_score表刪除資料時，student_go_wangba表刪除關聯資料DROP TRIGGER IF EXISTS del_score；create trigger del_scoreafter delete on student_score for each row begin DELETE FROM student_go_wangba where SID = old。SID；end；

查詢一下我的觸發器（

show triggers \G

）：

執行觸發器，從student表中刪除一條資料：

DELETE FROM `student` where NAME = ‘朱志鵬’

結果如下圖所示：

同時刪除三個資料，觸發器正確執行了。朱志鵬同學資料已經木得了~注意：建立觸發器和表一樣，建議增加判斷：

DROP TRIGGER IF EXISTS `del_stu`

；

# Update觸發器 - 實時更新

跟Insert觸發器、Delete觸發器同理

，這裡不再贅述

四、觸發器有哪些缺陷 @江湖中人

下面透過參考知乎、CSDN論壇、天涯社群，簡單講解幾個內容供大家參考：

問題一：觸發器有哪些限制和注意事項

回答：

觸發器會有以下兩種限制：

觸發程式不能呼叫將資料返回客戶端的儲存程式，也不能使用採用CALL語句的動態SQL語句，但是允許儲存程式透過引數將資料返回觸發程式，也就是儲存過程或者函式透過OUT或者INOUT型別的引數將資料返回觸發器是可以的，但是不能呼叫直接返回資料的過程。

不能再觸發器中使用以顯示或隱式方式開始或結束事務的語句，如START TRANS-ACTION，COMMIT或ROLLBACK。

注意事項：

MySQL的觸發器是按照BEFORE觸發器、行操作、AFTER觸發器的順序執行的，其中任何一步發生錯誤都不會繼續執行剩下的操作，如果對事務表進行的操作，如果出現錯誤，那麼將會被回滾，如果是對非事務表進行操作，那麼就無法回滾了，資料可能會出錯。

問題二：大型系統必須得要儲存過程和觸發器嗎？

回答1：

我們先要弄清楚二個問題：

什麼是大型系統？

你討論的是什麼領域的應用，可以大致分為二種：網際網路、企業內部

接下來給你舉一些例子：

SAP、peopleSoft、ERP等企業級別應用

一般情況下，會使用儲存過程和觸發器，減少開發成本，畢竟其業務邏輯修改頻繁，而且為通用，很多時候會把一些業務邏輯編寫成儲存過程，像Oracle會寫成包，比儲存過程更強大。

另外一個原因是伺服器的負載是可控，也即系統的訪問人數首先是可控的，沒有那麼大，而且這些資料又非常關鍵，為此往往使用的裝置也比較好，多用儲存櫃子支撐資料庫。

另外一類網際網路行業的

比如淘寶、知乎、微博等，資料庫的壓力是非常大的，也往往會最容易成為瓶頸，而且多用PC伺服器支撐，使用者量的增速是不可控的，同時線上訪問的使用者量也是不可控的，為此肯定會把業務邏輯放到其他語言的程式碼層，而且可以藉助一些LVS等型別軟硬體做負載均衡，以及平滑增減Web層的伺服器，從而達到線性的增減而支援大規模的訪問。

所以不管你的這個系統是否龐大，首先要分業務支援的物件，系統最可能容易出現瓶頸的地方在那？

當然也不是說網際網路行業的應用就絕對不用儲存過程，這個也不對，曾在阿里做的Oracle遷移MySQL系統確實用了，因為歷史的原因，另外還有一些新系統也有用，比如晚上進行定期的資料統計的一些操作，不過有量上的控制。儲存過程是好東西，要分場景，分業務型別來用就可以把握好。

回答2：

肯定不能一刀切的說能用或者不能用，不同型別的系統、不同的規模、不同的歷史原因都會有不同的解決方案。

一般情況下，Web應用的瓶頸常在DB上，所以會盡可能的減少DB做的事情，把耗時的服務做成Scale Out，這種情況下，肯定不會使用儲存過程；而如果只是一般的應用，DB沒有效能上的問題，在適當的場景下，也可以使用儲存過程。

至於觸發器，我是知道有這東西但從來沒用過。我希望風險可控，遇到問題能夠快速的找到原因，儘可能不會去使用觸發器。

回答3：

PLSQL可以大大降低parse/exec 百分比；

儲存過程可以自動完成靜態SQL variable bind；

儲存過程大大減少了JDBC網路傳輸與互動，速度快；

oracle 中儲存過程內部commit為非同步寫，一定程度上減少了等redo日誌落地時間；

儲存過程最大問題就是給資料庫開發工作壓力太大，另外架構升級時候會比較難解耦；

觸發器不推薦使用，觸發操作能在業務層解決就在業務層解決，否則很難維護，而且容易產生死鎖。

問題三：為什麼大家都不推薦使用MySQL觸發器而用儲存過程？

回答1：

儲存過程和觸發器二者是有很大的聯絡的，我的一般理解就是觸發器是一個隱藏的儲存過程，因為它不需要引數，不需要顯示呼叫，往往在你不知情的情況下已經做了很多操作。從這個角度來說，由於是隱藏的，無形中增加了系統的複雜性，非DBA人員理解起來資料庫就會有困難，因為它不執行根本感覺不到它的存在。

再有，涉及到複雜的邏輯的時候，觸發器的巢狀是避免不了的，如果再涉及幾個儲存過程，再加上事務等等，很容易出現死鎖現象，再除錯的時候也會經常性的從一個觸發器轉到另外一個，級聯關係的不斷追溯，很容易使人頭大。其實，從效能上，觸發器並沒有提升多少效能，只是從程式碼上來說，可能在coding的時候很容易實現業務，所以我的觀點是：摒棄觸發器！觸發器的功能基本都可以用儲存過程來實現。

在編碼中儲存過程顯示呼叫很容易閱讀程式碼，觸發器隱式呼叫容易被忽略。

儲存過程的致命傷在於移植性，儲存過程不能跨庫移植，比如事先是在mysql資料庫的儲存過程，考慮效能要移植到oracle上面那麼所有的儲存過程都需要被重寫一遍。

回答2：

這種東西只有在併發不高的專案，管理系統中用。如果是面向使用者的高併發應用，都不要使用。

觸發器和儲存過程本身難以開發和維護，不能高效移植。

觸發器完全可以用事務替代。

儲存過程可以用後端指令碼替代。