所有知識體系文章，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觸發器呢？原因很簡單，是因為需要擴展自己的知識儲備，開發中的使用問題和是否被大家摒棄，不是你拒絕學習知識的理由，之所以存在就有它存在的道理，我們在學習的道路中不斷擴充自己的知識儲備即可，

假如有一天你的同事聊起觸發器，你也能和他們聊聊你對觸發器的見解是哈？如果你根據從未了解過此知識呢？那性質就不一樣了，相信大家都懂吧！

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/29424.html

標籤：MySQL

上一篇：mysql查詢優化

下一篇：SQL--SQL詳解（DDL，DML，DQL，DCL）

MySQL觸發器的詳細教學與實戰分析

MySQL觸發器

一、什么是觸發器

二、觸發器的作用

三、觸發器創建的四要素

四、觸發器的使用語法

五、觸發器的基本使用

5.1 基本使用步驟

5.2 查看和洗掉已有的觸發器

5.3 for each row

六、訂單與庫存關系場景

七、觸發器中參考行變數

7.1 old和new物件語法

7.2 old和new物件應用

八、綜合案例

8.1 創建表、插入表資料

8.2 添加學生案例

8.3 洗掉學生案例

8.4 洗掉班級案例

8.5 觸發器沖突問題

九、觸發器性能和使用分析（必讀）