所有知識體系文章，GitHub已收錄，歡迎老闆們前來Star！
GitHub地址： https://github.com/Ziphtracks/JavaLearningmanual
MySQL觸發器
一、什麼是觸發器
觸發器（trigger）是MySQL提供給程式設計師和資料分析員來保證資料完整性的一種方法，它是與表事件相關的特殊的儲存過程，它的執行不是由程式呼叫，也不是手工啟動，而是由事件來觸發，比如當對一個表進行操作（insert，delete， update）時就會啟用它執行。簡單理解為：你執行一條sql語句，這條sql語句的執行會自動去觸發執行其他的sql語句。
二、觸發器的作用
- 可在寫入資料表前，強制檢驗或轉換資料。
- 觸發器發生錯誤時，異動的結果會被撤銷。
- 部分資料庫管理系統可以針對資料定義語言（DDL）使用觸發器，稱為DDL觸發器。
- 可依照特定的情況，替換異動的指令 (INSTEAD OF)。
三、觸發器建立的四要素
- 監視地點（table）
- 監視事件（insert、update、delete）
- 觸發時間（after、before）
- 觸發事件（insert、update、delete）
四、觸發器的使用語法
語法：
before/after： 觸發器是在增刪改之前執行，還是之後執行
delete/insert/update： 觸發器由哪些行為觸發（增、刪、改）
on 表名： 觸發器監視哪張表的（增、刪、改）操作
觸發SQL程式碼塊： 執行觸發器包含的SQL語句
1CREATE TRIGGER 觸發器名
2BEFORE|AFTER DELETE|INSERT|UPDATE
3ON 表名 FOR EACH ROW
4BEGIN
5觸發SQL程式碼塊;
6END;
注意： 觸發器也是儲存過程程式的一種，而觸發器內部的執行SQL語句是可以多行操作的，所以在MySQL的儲存過程程式中，要定義結束符。
如果MySQL儲存過程不瞭解的小夥伴，可以參考此文面向MySQL儲存過程程式設計，文章中詳細講解了MySQL儲存過程的優勢和語法等等，相信你會在其中得以收穫。
1# 設定MySQL執行結束標誌，預設為;
2delimiter //
五、觸發器的基本使用
5.1 基本使用步驟
首先，我先展示一下建立的兩張表，因為建立的表很簡單，這裡我沒有提供庫表操作的SQL命令。
tb_class
employee
其次，建立了一個含有update操作的儲存過程
1delimiter //
2create procedure update_emp(in i int, in p int)
3begin
4 update employee set phone = p where id = i;
5end //
再建立一個觸發器
分析： 觸發器名稱為t1，觸發時間為after，監視動作為update，監視表為employee表。彙總一起解釋這個觸發器就是：建立一個觸發器名稱為t1的觸發器，觸發器監視employee表執行update（更新）操作後，就開始執行觸發器內部SQL語句
update tb_class set num = num + 1 where id = 1;。簡單來說就是一個監視一個表的增、刪、改操作並設定操作前後時間，在設定時間的範圍內對另外一個表進行其他操作。
如果你學到這裡還是一知半解，後面我會講解一個訂單與庫存的資料關係，到那時候你就會明白了！
1delimiter //
2# 建立觸發器，觸發器名稱為t1
3create trigger t1
4 # 觸發器執行在update操作之後
5 after update
6 # 監視employee表
7 on employee
8 for each row
9begin
10 # 觸發執行的SQL語句
11 update tb_class set num = num + 1 where id = 1;
12end //
最後呼叫函式，並檢視、分析結果
1call update_emp(2, 110);
觸發器在此場景的作用分析
當employee表發生update操作時，觸發器就對tb_class表中的num值做修改。
執行結果發現，我們在使用函式將employee表中id為2員工的phone修改為110後，觸發器監視到employee表中發生了update更新操作，就執行了內部SQL語句，也就是將tb_class表中id為1的num值自增1。
5.2 檢視和刪除已有的觸發器
檢視已有觸發器：
show triggers刪除已有觸發器：
drop trigger 觸發器名稱
5.3 for each row
這裡擴充套件，在oracle觸發器中，觸發器分為行觸發器和語句觸發器。也就是說，假設你監視一個修改操作，它修改了1000行程式碼，在Oracle中觸發器會觸發1000次。
在oracle中，for each row如果不寫，無論update語句一次影響了多少行，都只執行一次觸發事件。
而MySQL中，不支援語句級觸發器，所以在MySQL中並不需要在意。
六、訂單與庫存關係場景
訂單與庫存的關係： 使用者下訂單，意味著建立該商品訂單，該商品訂單中的商品數量為1，庫存中的該商品數量-1。往往訂單表和庫存表中的數量是同時操作的，所以我們這裡可以用觸發器。
觸發器應用： 關於訂單表，下訂單肯定是涉及到insert插入資料數量的操作。我們可以建立一個監視訂單表insert操作後執行庫存表數量-1的觸發器來完成訂單與庫存表的同時修改。
建立表，並在表中新增幾條資料：
1create table goods(
2 gid int,
3 name varchar(20),
4 num smallint
5);
6create table ord(
7 oid int,
8 gid int,
9 much smallint
10);
11insert into goods values(1,'cat',40);
12insert into goods values(2,'dog',63);
13insert into goods values(3,'pig',87);
建立觸發器
1create trigger t1
2after
3insert
4on ord
5for each row
6begin
7 update goods set num = num - 1 where gid = 1;
8end$
該觸發器意為，使用者不管下什麼訂單，都會把商品編號為1的商品的庫存減去1。
七、觸發器中引用行變數
7.1 old和new物件語法
- 在觸發目標上執行insert操作後會有一個新行，如果在觸發事件中需要用到這個新行的變數，可以用new關鍵字表示
- 在觸發目標上執行delete操作後會有一箇舊行，如果在觸發事件中需要用到這個舊行的變數，可以用old關鍵字表示
- 在觸發目標上執行update操作後原紀錄是舊行，新記錄是新行，可以使用new和old關鍵字來分別操作
| 觸發語句 | old | new |
|---|---|---|
| insert | 所有欄位都為空 | 將要插入的資料 |
| update | 更新以前該行的值 | 更新後的值 |
| delete | 刪除以前該行的值 | 所有欄位都為空 |
7.2 old和new物件應用
關於old和new物件的應用，我在這裡沒有展開演示。只是將第八章的綜合案例結合了old和new物件實現。綜合案例中詳細講解了MySQL觸發器的使用！
八、綜合案例
8.1 建立表、插入表資料
tb_class為幼兒園班級表，其中cid為唯一主鍵，cname為大、中、小班班級標準，stuNo為班級標準內的學生個數。插入大、中、小班標準，初始化兩名學生在大班。
tb_stu為幼兒園學生表，其中sid為唯一主鍵，sname為學生性名，cno為所在班級標準的外來鍵。插入兩條資料並初始化這兩名學生在大班，因為我們在班級表中初始化了兩名學生在大班嘛，所以要做此操作。
1create table tb_class
2(
3 cid int auto_increment
4 primary key,
5 cname varchar(32) not null,
6 stuNo int not null
7);
8
9INSERT INTO temp.tb_class (cname, stuNo) VALUES ('大班', 2)
10INSERT INTO temp.tb_class (cname, stuNo) VALUES ('中班', 0)
11INSERT INTO temp.tb_class (cname, stuNo) VALUES ('小班', 0)
12
13create table tb_stu
14(
15 sid int auto_increment
16 primary key,
17 sname varchar(32) not null,
18 cno int not null
19);
20
21INSERT INTO temp.tb_stu (sname, cno) VALUES ('Ziph', 1)
22INSERT INTO temp.tb_stu (sname, cno) VALUES ('Join', 1)
8.2 新增學生案例
在此表結構中，如果一位新同學來到學校學習，意味著某一個班級中會多出一名學生。假設Marry同學去小班學習，其表結構的變化為：tb_stu表中新增一條Marry的記錄（注：cno = 3），tb_class表中小班記錄的stuNo = 0修改為stuNo = 1
先建立一個新增學生的儲存過程
1# 新增學生函式
2delimiter //
3# 建立儲存過程，傳入學生性名和班級引數
4create procedure add_stu(in in_sname varchar(32), in in_cno int)
5begin
6 # 插入記錄
7 insert into tb_stu (sname, cno) values (in_sname, in_cno);
8end //
建立觸發器
注意： 在更新學生數量SQL語句中，有一段cid = new.cno的SQL語句。這裡我解釋一下，new代表產生的新物件，將cid主鍵與新增Marry記錄後產生的新紀錄物件的cno外來鍵關聯。（因為insert後產生的是新紀錄物件嘛，所以用new）
1# 觸發器
2# 建立名稱為t_add_stu的觸發器
3create trigger t_add_stu
4 # 設定在insert操作之後觸發
5 after
6 insert
7 # 監視tb_stu的insert操作
8 on tb_stu
9 for each row
10begin
11 # 更新學生數量(cid為tb_class表中主鍵，cno為tb_stu表中外來鍵)
12 update tb_class set stuNo = stuNo + 1 where cid = new.cno;
13end //
宣告回結束符
1delimiter ;
插入Marry學生記錄到資料庫表中
1call add_stu('Marry', 3);
執行結果就是當插入Marry學生記錄的同時也修改了班級表中的小班學生數量。
8.3 刪除學生案例
刪除學生與新增學生十分相似，刪除學生相當於是新增學生的逆過程。如果以為學生退學了或者讀完了幼兒園離開學校了，就意味著班級中少了一位學生。假設Join同學讀完了大班結束了幼兒園階段的學習將要幼兒園去上小學，其表結構變化為：tb_stu刪除Join這條記錄（注：sid = 2），tb_class將修改Join所在大班班級級別的stuNo，即stuNo = stuNo - 1
先建立一個刪除學生的儲存過程
1# 刪除學生
2delimiter //
3create procedure delete_stu(in in_sid int)
4begin
5 delete from tb_stu where sid = in_sid;
6end //
建立觸發器
注意： 在更新學生數量的時候，書寫了此段SQL語句cid = OLD.cno。該語句使用old物件，意為Join學生的記錄沒有了，但是使用觸發器同步修改tb_class表中的大班學生數量還需要用到關聯Join學生所在記錄的外來鍵cno，使用old來句點出來的cno就是刪除之前Join那一條學生記錄的cno。（如果我們用new，該記錄還存在嗎？該記錄的cno還存在嗎？答案是都不存在了！）
1# 觸發器
2# 建立觸發器名稱為t_delete_stu的觸發器
3create trigger t_delete_stu
4 # 設定在delete操作之後觸發
5 after
6 delete
7 # 監視tb_stu表的delete操作
8 on tb_stu
9 for each row
10begin
11 # 更新學生數量(cid為tb_class表中主鍵，cno為tb_stu表中外來鍵)
12 update tb_class set stuNo = stuNo - 1 where cid = OLD.cno;
13end //
宣告回結束符
1delimiter ;
刪除Jion學生記錄
1call delete_stu(2);
執行結果為Join記錄在資料庫的表中消失了，而大班的學生數量也減掉了1。
8.4 刪除班級案例
因為我已經詳細講解了新增學生與刪除學生，所以刪除班級我就不再作過多的贅述了。那就直接說核心內容吧。刪除一個班級級別比如：刪除小班之前要把小班內的所有學生也被刪除了，因為兩個表是主外來鍵關聯的。如果只刪除了小班，而沒有刪除小班內的所有學生，那麼原小班內的所有學生現在屬於哪個班級呢，就不知道了吧！所以要在刪除小班之前刪除小班內的所有學生。
1# 建立刪除班級的儲存過程
2delimiter //
3create procedure delete_class(in in_cid int)
4begin
5 delete from tb_class where cid = in_cid;
6end //
7
8# 建立觸發器名稱為t_delete_class的觸發器
9create trigger t_delete_class
10 # 作用在delete操作之前
11 before
12 delete
13 # 監視tb_class表中的delete操作
14 on tb_class
15 for each row
16begin
17 # 同時刪除所有該原班級cid的所有學生
18 delete from tb_stu where cno = OLD.cid;
19end //
20
21# 將結束符宣告為;
22delimiter ;
23
24# 刪除小班班級別
25call delete_class(3);
執行結果為既刪除了小班，又刪除小班內的所有學生。
8.5 觸發器衝突問題
觸發器衝突問題其實就是關聯問題。為什麼這麼說呢？就說以下剛才這三個案例中出現的觸發器衝突問題。
如果我們在寫觸發器的時候，將新增學生、刪除學生和刪除班級的觸發器都寫在一個查詢模板中。你會發現當你在刪除班級的時候，會報錯。顯示如下資訊：
這是為什麼呢？
仔細想想，我們將在案例中有兩個是同一個表中的刪除觸發器。刪除班級的觸發器中定義的是刪除班級時觸發刪除學生，而刪除學生的觸發器中定義的是班級人數減一。你發現了沒，觸發器被連著觸發了。如下變化：
我們通過刪除班級案例瞭解了，刪除班級之前需要把班級內所有學生刪除掉。正因為如此，我們在刪除班級之前已經把所有學生都刪除了，導致在刪除學生的時候觸發了班級人數減一的觸發器，該觸發器在執行過程中修改了已經被刪除班級的學生人數。這問題就出在這裡了，班級已經刪除了，怎麼修改一個本就沒有的班級內的人數呢？對吧！
解決觸發器衝突
為解決這個場景的觸發器衝突問題，我們只能取捨一個觸發器。於是，就通過命令刪除了刪除學生案例中使用的那個觸發器，刪除後刪除班級就可以成功執行觸發了！
1drop trigger t_delete_stu;
注意： 由於存在觸發器衝突問題，我們在實際開發中需要認真考量定義觸發器！
九、觸發器效能和使用分析（必讀）
各大論壇等等，相信在大家的文章中都不推薦使用觸發器，而是推薦使用儲存過程程式，這是為什麼呢？
首先，儲存過程程式分為儲存過程、儲存過程函式和觸發器。也就是說這三種都是儲存過程的使用都是儲存過程的表現形式。
如果場景在資料量和併發量都很大的情況下，使用觸發器、儲存過程再加上幾個事務等等，很容易出現死鎖。而且在使用觸發器的時候，也會出現衝突，出現問題時，我們需要追溯的程式碼就需要從一個觸發器到另一個觸發器……從而影響開發效率。從效能上看，觸發器也是儲存過程程式的一種，它也並沒有展現出多少效能上的優勢。由於觸發器寫起來比較隱蔽，容易被開發人員忽略，而且隱式呼叫觸發器不易於排除依賴，對後期維護不是很友好！
所以在開發中，觸發器是很少用到的。那為什麼我還花時間大篇幅的講解MySQL觸發器呢？原因很簡單，是因為需要擴充套件自己的知識儲備。開發中的使用問題和是否被大家摒棄，不是你拒絕學習知識的理由。之所以存在就有它存在的道理，我們在學習的道路中不斷擴充自己的知識儲備即可。
假如有一天你的同事聊起觸發器，你也能和他們聊聊你對觸發器的見解是哈？如果你根據從未了解過此知識呢？那性質就不一樣了，相信大家都懂吧！