MySQL觸發(fā)器介紹
前言：
在學習 MySQL 的過程中，可能你了解過觸發(fā)器的概念，不清楚各位是否有詳細的去學習過觸發(fā)器，最近看了幾篇關(guān)于觸發(fā)器的文檔，分享下 MySQL 觸發(fā)器相關(guān)知識。
1.觸發(fā)器簡介
觸發(fā)器即 triggers ，它是與表有關(guān)的數(shù)據(jù)庫對象，在滿足定義條件時觸發(fā)，并執(zhí)行觸發(fā)器中定義的語句集合。它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，比如當對一個表進行操作（ insert，delete， update）時就會激活它執(zhí)行。觸發(fā)器經(jīng)常用于加強數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。
參考官方文檔，觸發(fā)器創(chuàng)建語法模板如下：
CREATE
[DEFINER = user]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name觸發(fā)器只能創(chuàng)建在永久表上，不能對臨時表或視圖創(chuàng)建觸發(fā)器。觸發(fā)器的名稱在單個數(shù)據(jù)庫內(nèi)是唯一的。參考上面創(chuàng)建語句，觸發(fā)器創(chuàng)建有幾點要素，下面簡要說明下：
trigger_time：是觸發(fā)動作時間，可以是 BEFORE 或 AFTER ，表示觸發(fā)器在要修改的每一行之前或之后激活。
trigger_event：指示激活觸發(fā)器的操作類型。這些 trigger_event 值是被允許的：
insert：只要向表中插入新行，觸發(fā)器就會激活。例如 insert 、load data、replace 語句。
update：更改表中某一行數(shù)據(jù)時激活觸發(fā)器。例如 update 語句。
delete：從表中刪除某一行數(shù)據(jù)時激活觸發(fā)器。例如 delete 和 replace 語句。表上的 DROP TABLE 和 TRUNCATE TABLE 語句不會激活此觸發(fā)器，因為它們不使用 delete ，刪除分區(qū)也不會激活 delete 觸發(fā)器。
trigger_body：是觸發(fā)器激活時要執(zhí)行的語句。如果要執(zhí)行多個語句，可使用 BEGIN…END 復(fù)合語句結(jié)構(gòu)。在觸發(fā)器主體中，可以使用 old 和 new 來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容。
2.觸發(fā)器具體操作
下面來看下觸發(fā)器相關(guān)的具體操作：
# 創(chuàng)建表 創(chuàng)建觸發(fā)器
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;//
mysql> delimiter ;
# 驗證觸發(fā)器作用
mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 14.98 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)
mysql> update account set amount = 114.98 where acct_num = 137;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 100.00 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)
# 查看觸發(fā)器
mysql> show triggers;
# 刪除觸發(fā)器
mysql> drop trigger if exists upd_check;
# 查看數(shù)據(jù)庫實例中所有觸發(fā)器
SELECT
a.TRIGGER_SCHEMA,
a.TRIGGER_NAME,
a.ACTION_TIMING,
a.EVENT_OBJECT_TABLE,
a.EVENT_MANIPULATION
FROM
information_schema.`TRIGGERS` a
WHERE
a.TRIGGER_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );
delimiter // 設(shè)置MySQL執(zhí)行結(jié)束標志，默認為;上面展示了一些關(guān)于觸發(fā)器的基本操作，其實觸發(fā)器在生產(chǎn)環(huán)境中還是比較少見的，即使它能解決我們某些數(shù)據(jù)庫需求，因為觸發(fā)器的使用存在一系列的缺點，簡要總結(jié)幾點缺點如下：
使用觸發(fā)器實現(xiàn)的業(yè)務(wù)邏輯在出現(xiàn)問題時很難進行定位，特別是涉及到多個觸發(fā)器的情況下，會使后期維護變得困難。
大量使用觸發(fā)器容易導(dǎo)致代碼結(jié)構(gòu)被打亂，增加了程序的復(fù)雜性，
如果需要變動的數(shù)據(jù)量較大時，觸發(fā)器的執(zhí)行效率會非常低。
觸發(fā)器隱式調(diào)用容易被忽略，出現(xiàn)問題不好排查。
但是觸發(fā)器也并不是一無用處，比如我們不想讓人刪除或更新這個表的數(shù)據(jù)，可以用觸發(fā)器實現(xiàn)，下面的一些場景可能對你有所啟發(fā)：
# 禁止刪除數(shù)據(jù) 即使你有權(quán)限
mysql> select * from student;
+--------------+------+--------+-------+-------+
| increment_id | s_id | s_name | s_sex | s_age |
+--------------+------+--------+-------+-------+
| 1 | 1001 | sdfsd | 男 | 18 |
| 2 | 1003 | zsdfsd | 女 | 19 |
+--------------+------+--------+-------+-------+
2 rows in set (0.00 sec)
mysql> delimiter //
mysql> CREATE TRIGGER `tri_delstu` BEFORE DELETE ON `student` FOR EACH ROW begin
-> declare msg varchar(255);
-> set msg="不允許刪除學生信息";
-> SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
-> end; //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> delete from student where s_id = 1003;
ERROR 1644 (HY000): 不允許刪除學生信息
# 禁止更新某個字段
mysql> delimiter //
mysql> CREATE TRIGGER trg__updateSid BEFORE UPDATE ON `student`
-> FOR EACH ROW
-> BEGIN
-> DECLARE msg VARCHAR(100);
-> IF NEW.s_id <> OLD.s_id THEN
-> SET msg='不允許修改學號';
-> SIGNAL SQLSTATE 'HY000' SET message_text = msg;
-> END IF;
-> END; //
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> update student set s_id = 1002 where increment_id = 2;
ERROR 1644 (HY000): 不允許修改學號
# 限制修改范圍
mysql> delimiter //
mysql> CREATE TRIGGER `tri_update_age` BEFORE UPDATE ON `student` FOR EACH ROW BEGIN
-> DECLARE msg VARCHAR(20);
-> IF (NEW.s_age<0) THEN
-> set msg="年齡不能小于0";
-> signal sqlstate 'HY000' set message_text=msg;
-> END IF;
-> END; //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> update student set s_age=10 where s_id = 1001;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set s_age=-10 where s_id = 1001;
ERROR 1644 (HY000): 年齡不能小于0總結(jié)：
本篇文章簡要介紹了觸發(fā)器的定義及使用案例。在業(yè)務(wù)邏輯復(fù)雜或表變動比較頻繁的系統(tǒng)還是不推薦使用觸發(fā)器，當然它也是有自己的應(yīng)用場景，無論怎樣，觸發(fā)器的邏輯總是越簡單越好，我們應(yīng)該讓數(shù)據(jù)庫做它擅長做的事，不能想著所有邏輯都在數(shù)據(jù)庫層面實現(xiàn)。
推薦閱讀
（點擊標題可跳轉(zhuǎn)閱讀）