觸發(fā)器（trigger）：監(jiān)視某種情況，并觸發(fā)某種操作，它是提供給程序員和數(shù)據(jù)分析員來(lái)保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲(chǔ)過(guò)程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動(dòng)，而是由事件來(lái)觸發(fā)，例如當(dāng)對(duì)一個(gè)表進(jìn)行操作（ insert，delete， update）時(shí)就會(huì)激活它執(zhí)行。觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。

網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)公司！專(zhuān)注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、成都微信小程序、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了愛(ài)民免費(fèi)建站歡迎大家使用！

基本理解：

\1. 使用場(chǎng)合：

觸發(fā)器是基于事件的，主要的事件也就是MySQL的增刪改操作，即insert,delete,update。

\2. 觸發(fā)器的命名

Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.

因?yàn)橛|發(fā)器在單表的命名空間內(nèi)，所以同一個(gè)表的觸發(fā)器名稱(chēng)需要不同。不同表可以有相同的觸發(fā)器名稱(chēng)。

\3. 觸發(fā)器執(zhí)行順序

如果有相同的update（或者delete,insert）觸發(fā)器，就會(huì)按照創(chuàng)建的時(shí)間來(lái)執(zhí)行。

而FOLLOWS 和 PRECEDES 可以修改trigger的執(zhí)行順序

例如官方的案例：

mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account

FOR EACH ROW PRECEDES ins_sum

SET

@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),

@withdrawals = @withdrawals + IF(NEW.amount

Query OK, 0 rows affected (0.01 sec)

ins_transaction和ins_sum分別是兩個(gè)觸發(fā)器的名稱(chēng)。

4. 觸發(fā)器的作用：

\1. 安全性?？梢曰跀?shù)據(jù)庫(kù)的值使用戶具有操作數(shù)據(jù)庫(kù)的某種權(quán)利。

1）可以基于時(shí)間限制用戶的操作，例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫(kù)數(shù)據(jù)。

2）可以基于數(shù)據(jù)庫(kù)中的數(shù)據(jù)限制用戶的操作，例如不允許單個(gè)商品的購(gòu)買(mǎi)量大于一個(gè)固定值。

\2. 審計(jì)?？梢愿櫽脩魧?duì)數(shù)據(jù)庫(kù)的操作。

1）審計(jì)用戶操作數(shù)據(jù)庫(kù)的語(yǔ)句。

2）把用戶對(duì)數(shù)據(jù)庫(kù)的更新寫(xiě)入審計(jì)表。

這一塊因?yàn)楸救藳](méi)有用過(guò)，就不贅述了，以后有機(jī)會(huì)來(lái)補(bǔ)充

\3. 實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則

實(shí)現(xiàn)非標(biāo)準(zhǔn)的數(shù)據(jù)完整性檢查和約束。觸發(fā)器可產(chǎn)生比規(guī)則更為復(fù)雜的限制。與規(guī)則不同，觸發(fā)器可以引用列或數(shù)據(jù)庫(kù)對(duì)象。例如，觸發(fā)器可回退任何企圖吃進(jìn)超過(guò)自己保證金的期貨。

\4. 實(shí)現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)相關(guān)完整性規(guī)則。

1）觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)中相關(guān)的表進(jìn)行連環(huán)更新。這是用得比較多的一種實(shí)現(xiàn)功能。

2） 觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化，取消試圖進(jìn)行數(shù)據(jù)更新的事務(wù)。當(dāng)插入一個(gè)與其主健不匹配的外部鍵時(shí)，這種觸發(fā)器會(huì)起作用。

下面的例子我會(huì)比較詳細(xì)的描述這兩個(gè)特性。

\5. 同步實(shí)時(shí)地復(fù)制表中的數(shù)據(jù)。

\6. 自動(dòng)計(jì)算數(shù)據(jù)值，如果數(shù)據(jù)的值達(dá)到了一定的要求，則進(jìn)行特定的處理。

例如，如果公司的帳號(hào)上的資金低于5萬(wàn)元?jiǎng)t立即給財(cái)務(wù)人員發(fā)送警告數(shù)據(jù)。

**（**1）插入數(shù)據(jù)：

當(dāng)用戶添加一個(gè)訂單的時(shí)候，我們需要對(duì)商品表格中的庫(kù)存（storage）進(jìn)行相應(yīng)的改動(dòng)

mysql> create trigger shop_goods
   -> after insert on shoppingcar
   -> for each row
   -> update goods set storage=storage-new.amount where id=new.g_id
   -> ;
Query OK, 0 rows affected (0.03 sec)

mysql>insert into shoppingcar values(1,1,2);

查詢結(jié)果：

goods表
+-------+---------+-------------+---------+
| id    | gname   | description | storage |
+-------+---------+---------- --+---------+
|  1    |  huawei |  rongyao9   |    198  |
|  2    |  iphone |  iphoneX    |    100  |
+-------+---------+-------------+---------+

shoppingcar表
+----- -+----- -+--------+
| u_id  | g_id  | amount |
+-------+-------+--------+
|  1    |   1   |  2     |
+-------+-------+--------+

**關(guān)于**new和old的使用

new表示新的數(shù)據(jù)行，而old表示舊的數(shù)據(jù)行

**（**2）刪除數(shù)據(jù)

例如，用戶撤銷(xiāo)一個(gè)訂單的時(shí)候，我們需要將商品的數(shù)量加回去

mysql> create trigger shop_good1
   -> after delete on shoppingcar
   -> for each row
   -> update goods set storage=storage+old.amount where id=old.g_id;
Query OK, 0 rows affected (0.01 sec)
刪除前：
mysql> select  * from goods;
+-----+---------+-------------+---------+
| id  | gname   | description | storage |
+-----+---------+-------------+---------+
|  1  |  huawei | rongyao9    |    198  |
|  2  |  iphone | iphoneX     |    100  |
+-----+---------+-------------+---------+
2 rows in set (0.00 sec)


mysql> select  * from shoppingcar;
+------+------+--------+
| u_id | g_id | amount |
+------+------+--------+
|   1  |   1  |     2  |
+------+------+--------+
1 rows in set (0.00 sec)
刪除數(shù)據(jù)：
mysql> delete from shoppingcar where g_id=1;
Query OK, 1 row affected (0.03 sec)

結(jié)果：

**（**3）更新數(shù)據(jù)（可增可減）

當(dāng)用戶對(duì)想通過(guò)修改購(gòu)物車(chē)的數(shù)量來(lái)修改自己購(gòu)買(mǎi)某種商品的數(shù)量，那么，我們的庫(kù)存也需要跟著改動(dòng)。

mysql> create trigger shop_good2
   -> after update on shoppingcar
   -> for each row
   -> update goods set storage=storage-new.amount+old.amount where id=new.g_id/old.g_id;
Query OK, 0 rows affected (0.14 sec)

查看觸發(fā)器命令

show triggers

這個(gè)命令只能看到都有哪些的triggers，而看不到trigger的具體信息。

所有觸發(fā)器信息都存儲(chǔ)在information_schema數(shù)據(jù)庫(kù)下的triggers表中,可以使用SELECT語(yǔ)句查詢。如果有很多個(gè)觸發(fā)器，最好通過(guò)TRIGGER_NAME字段指定查詢某一個(gè)觸發(fā)器。

例如：

SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME=’XXX’;

（4）限制條件

條件限制對(duì)一些涉及到金額的場(chǎng)合（如開(kāi)篇提到的）非常重要，在電商的限購(gòu)數(shù)額中也會(huì)有應(yīng)用。

trigger利用delimiter,begin和if語(yǔ)句塊實(shí)現(xiàn)限制條件。

例如：
mysql> delimiter //          
mysql> create trigger shop_limit before update on shoppingcar
   -> for each row
   -> begin
   ->   if new.amount>3 then
   ->      set new.amount=3;
   ->   elseif new.amountthen
   ->      set new.amount=0;
   ->   end if;
   -> end; //
mysql> delimiter ;

條件語(yǔ)句程序塊用begin和end包裹起來(lái)實(shí)現(xiàn)

delimiter：切換結(jié)束符，因?yàn)椋皇荕ySQL中默認(rèn)的結(jié)束符，如果程序塊中出現(xiàn)；符號(hào)，就會(huì)引起沖突。最后要將結(jié)束符修改回來(lái)。注意delimiter與結(jié)束符之間有空格，否則會(huì)無(wú)法切換。

更新數(shù)據(jù)前：

更新數(shù)據(jù)：

mysql> update shoppingcar set amount=4 where u_id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

更新數(shù)據(jù)后：

購(gòu)買(mǎi)量無(wú)法超過(guò)3

關(guān)于觸發(fā)器與事務(wù)

對(duì)于事務(wù)表（Innodb），before語(yǔ)句后面的判斷語(yǔ)句失敗將會(huì)導(dǎo)致回滾事件語(yǔ)句執(zhí)行的所有更改。觸發(fā)器失敗會(huì)導(dǎo)致語(yǔ)句失敗，因此觸發(fā)器失敗也會(huì)導(dǎo)致回滾。對(duì)于非事務(wù)性表（MyISAM），無(wú)法執(zhí)行此類(lèi)回滾，因此盡管語(yǔ)句失敗，但在錯(cuò)誤點(diǎn)之前執(zhí)行的任何更改仍然有效。

關(guān)于觸發(fā)器的使用限制

觸發(fā)器執(zhí)有一些限制：

\1. 觸發(fā)器不能使用CALL 語(yǔ)句來(lái)將數(shù)據(jù)返回給客戶端或使用動(dòng)態(tài)SQL的存儲(chǔ)過(guò)程。但允許存儲(chǔ)過(guò)程通過(guò)OUT或INOUT 參數(shù)將數(shù)據(jù)返回到觸發(fā)器 。

\2. 觸發(fā)不能使用事務(wù)相關(guān)的語(yǔ)句，如 START TRANSACTION，COMMIT或ROLLBACK。因?yàn)橛|發(fā)器對(duì)update,delete,insert等事件做了處理，并且是按照before，SQL語(yǔ)句，after的順序來(lái)執(zhí)行的，一旦某一步出錯(cuò)，就會(huì)回滾數(shù)據(jù)。如果在觸發(fā)器中使用事務(wù)，就會(huì)產(chǎn)生矛盾。

網(wǎng)頁(yè)標(biāo)題：MySQL觸發(fā)器深入講解
轉(zhuǎn)載來(lái)于：http://uogjgqi.cn/article/dpcgpeh.html