[[414338]]

本文轉載自微信公眾號「MySQL技術」，作者MySQL技術。轉載本文請聯系MySQL技術公眾號。

1.觸發器簡介

觸發器即 triggers ，它是與表有關的數據庫對象，在滿足定義條件時觸發，并執行觸發器中定義的語句集合。它的執行不是由程序調用，也不是手工啟動，而是由事件來觸發，比如當對一個表進行操作( insert，delete， update)時就會激活它執行。觸發器經常用于加強數據的完整性約束和業務規則等。

參考官方文檔，觸發器創建語法模板如下：

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 

觸發器只能創建在永久表上，不能對臨時表或視圖創建觸發器。觸發器的名稱在單個數據庫內是唯一的。參考上面創建語句，觸發器創建有幾點要素，下面簡要說明下：

trigger_time：是觸發動作時間，可以是 BEFORE 或 AFTER ，表示觸發器在要修改的每一行之前或之后激活。

• trigger_event：指示激活觸發器的操作類型。這些 trigger_event 值是被允許的：
• insert：只要向表中插入新行，觸發器就會激活。例如 insert 、load data、replace 語句。
• update：更改表中某一行數據時激活觸發器。例如 update 語句。

delete：從表中刪除某一行數據時激活觸發器。例如 delete 和 replace 語句。表上的 DROP TABLE 和 TRUNCATE TABLE 語句不會激活此觸發器，因為它們不使用 delete ，刪除分區也不會激活 delete 觸發器。

trigger_body：是觸發器激活時要執行的語句。如果要執行多個語句，可使用 BEGIN…END 復合語句結構。在觸發器主體中，可以使用 old 和 new 來引用觸發器中發生變化的記錄內容。

2.觸發器具體操作

下面來看下觸發器相關的具體操作：

# 創建表 創建觸發器 
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 ; 
 
# 驗證觸發器作用 
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) 
 
# 查看觸發器 
mysql> show triggers; 
 
# 刪除觸發器 
mysql> drop trigger if exists upd_check; 
 
# 查看數據庫實例中所有觸發器 
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 // 設置MySQL執行結束標志，默認為; 

上面展示了一些關于觸發器的基本操作，其實觸發器在生產環境中還是比較少見的，即使它能解決我們某些數據庫需求，因為觸發器的使用存在一系列的缺點，簡要總結幾點缺點如下：

• 使用觸發器實現的業務邏輯在出現問題時很難進行定位，特別是涉及到多個觸發器的情況下，會使后期維護變得困難。
• 大量使用觸發器容易導致代碼結構被打亂，增加了程序的復雜性，
• 如果需要變動的數據量較大時，觸發器的執行效率會非常低。
• 觸發器隱式調用容易被忽略，出現問題不好排查。

但是觸發器也并不是一無用處，比如我們不想讓人刪除或更新這個表的數據，可以用觸發器實現，下面的一些場景可能對你有所啟發：

# 禁止刪除數據 即使你有權限 
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 

總結： 

本篇文章簡要介紹了觸發器的定義及使用案例。在業務邏輯復雜或表變動比較頻繁的系統還是不推薦使用觸發器，當然它也是有自己的應用場景，無論怎樣，觸發器的邏輯總是越簡單越好，我們應該讓數據庫做它擅長做的事，不能想著所有邏輯都在數據庫層面實現。