觸發器Trigger

一樣跳過

在實際開發中，我們經常會遇到這樣的情況：有 2 個或者多個相互關聯的表，如商品資訊和庫存資訊分別存放在 2 個不同的資料表中，我們在添加一條新商品記錄的時候，為了保證資料的完整性，必須同時在庫存表中添加一條庫存記錄。

這樣一來，我們就必須把這兩個關聯的操作步驟寫到程式裡面，而且要用事務包裹起來，確保這兩個操作成為一個原子操作，要麼全部執行，要麼全部不執行。要是遇到特殊情況，可能還需要對資料進行手動維護，這樣就很容易忘記其中的一步，導致資料缺失。

這個時候，咱們可以使用觸發器。**你可以創建一個觸發器，讓商品資訊資料的插入操作自動觸發庫存資料的插入操作。**這樣一來，就不用擔心因為忘記添加庫存資料而導致的資料缺失了。

1. 觸發器概述

MySQL從5.0.2版本開始支持觸發器。MySQL的觸發器和存儲過程一樣，都是嵌入到MySQL伺服器的一段程式。

觸發器是由事件來觸發某個操作，這些事件包括INSERT、UPDATE、DELETE事件。所謂事件就是指用戶的動作或者觸發某項行為。如果定義了觸發程式，當資料庫執行這些語句時候，就相當於事件發生了，就會自動激發觸發器執行相應的操作。

當對資料表中的資料執行插入、更新和刪除操作，需要自動執行一些資料庫邏輯時，可以使用觸發器來實現。

2. 觸發器的創建

2.1 創建觸發器語法

創建觸發器的語法結構是：

CREATE TRIGGER 觸發器名稱
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
觸發器執行的語句塊;

說明：

表名：表示觸發器監控的物件。
BEFORE|AFTER：表示觸發的時間。BEFORE 表示在事件之前觸發；AFTER 表示在事件之後觸發。
INSERT|UPDATE|DELETE：表示觸發的事件。
- INSERT 表示插入記錄時觸發；
- UPDATE 表示更新記錄時觸發；
- DELETE 表示刪除記錄時觸發。
觸發器執行的語句塊：可以是單條SQL語句，也可以是由BEGIN…END結構組成的複合陳述式塊。

2.2 代碼舉例

舉例1：

1、創建資料表：

CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
);


CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);

2、創建觸發器：創建名稱為before_insert的觸發器，向test_trigger資料表插入資料之前，向test_trigger_log資料表中插入before_insert的日誌資訊。

DELIMITER //

CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
	INSERT INTO test_trigger_log (t_log)
	VALUES('before_insert');

END //

DELIMITER ;

3、向test_trigger資料表中插入資料

INSERT INTO test_trigger (t_note) VALUES ('測試 BEFORE INSERT 觸發器');

4、查看test_trigger_log資料表中的資料

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log         |
+----+---------------+
|  1 | before_insert |
+----+---------------+
1 row in set (0.00 sec)

舉例2：

1、創建名稱為after_insert的觸發器，向test_trigger資料表插入資料之後，向test_trigger_log資料表中插入after_insert的日誌資訊。

DELIMITER //

CREATE TRIGGER after_insert
AFTER INSERT ON test_trigger
FOR EACH ROW
BEGIN
	INSERT INTO test_trigger_log (t_log)
	VALUES('after_insert');
END //

DELIMITER ;

2、向test_trigger資料表中插入資料。

INSERT INTO test_trigger (t_note) VALUES ('測試 AFTER INSERT 觸發器');

3、查看test_trigger_log資料表中的資料

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log         |
+----+---------------+
|  1 | before_insert |
|  2 | before_insert |
|  3 | after_insert  |
+----+---------------+
3 rows in set (0.00 sec)

**舉例3：**定義觸發器"salary_check_trigger"，基於員工表"employees"的INSERT事件，在INSERT之前檢查將要添加的新員工薪資是否大於他領導的薪資，如果大於領導薪資，則報sqlstate_value為’HY000’的錯誤，從而使得添加失敗。

DELIMITER //

CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
	DECLARE mgrsalary DOUBLE;
	SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;

	IF NEW.salary > mgrsalary THEN
		SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪資高於領導薪資錯誤';
	END IF;
END //

DELIMITER ;

上面觸發器聲明過程中的NEW關鍵字代表INSERT添加語句的新記錄。

3. 查看、刪除觸發器

3.1 查看觸發器

查看觸發器是查看資料庫中已經存在的觸發器的定義、狀態和語法資訊等。

方式1：查看當前資料庫的所有觸發器的定義

SHOW TRIGGERS\G

方式2：查看當前資料庫中某個觸發器的定義

SHOW CREATE TRIGGER 觸發器名

方式3：從系統庫information_schema的TRIGGERS表中查詢"salary_check_trigger"觸發器的資訊。

SELECT * FROM information_schema.TRIGGERS;

3.2 刪除觸發器

觸發器也是資料庫物件，刪除觸發器也用DROP語句，語法格式如下：

DROP TRIGGER  IF EXISTS 觸發器名稱;

4. 觸發器的優缺點

4.1 優點

1、觸發器可以確保資料的完整性。

假設我們用進貨單頭表（demo.importhead）來保存進貨單的總體資訊，包括進貨單編號、供應商編號、倉庫編號、總計進貨數量、總計進貨金額和驗收日期。

用進貨單明細表（demo.importdetails）來保存進貨商品的明細，包括進貨單編號、商品編號、進貨數量、進貨價格和進貨金額。

每當我們錄入、刪除和修改一條進貨單明細資料的時候，進貨單明細表裡的資料就會發生變動。這個時候，在進貨單頭表中的總計數量和總計金額就必須重新計算，否則，進貨單頭表中的總計數量和總計金額就不等於進貨單明細表中數量合計和金額合計了，這就是資料不一致。

為了解決這個問題，我們就可以使用觸發器，規定每當進貨單明細表有資料插入、修改和刪除的操作時，自動觸發 2 步操作：

1）重新計算進貨單明細表中的數量合計和金額合計；

2）用第一步中計算出來的值更新進貨單頭表中的合計數量與合計金額。

這樣一來，進貨單頭表中的合計數量與合計金額的值，就始終與進貨單明細表中計算出來的合計數量與合計金額的值相同，資料就是一致的，不會互相矛盾。

2、觸發器可以説明我們記錄操作日誌。

利用觸發器，可以具體記錄什麼時間發生了什麼。比如，記錄修改會員儲值金額的觸發器，就是一個很好的例子。這對我們還原操作執行時的具體場景，更好地定位問題原因很有幫助。

3、觸發器還可以用在運算元據前，對資料進行合法性檢查。

比如，超市進貨的時候，需要庫管錄入進貨價格。但是，人為操作很容易犯錯誤，比如說在錄入數量的時候，把條碼掃進去了；錄入金額的時候，看串了行，錄入的價格遠超售價，導致帳面上的巨虧……這些都可以通過觸發器，在實際插入或者更新操作之前，對相應的資料進行檢查，及時提示錯誤，防止錯誤資料進入系統。

4.2 缺點

1、觸發器最大的一個問題就是可讀性差。

因為觸發器存儲在資料庫中，並且由事件驅動，這就意味著觸發器有可能不受應用層的控制。這對系統維護是非常有挑戰的。

比如，創建觸發器用於修改會員儲值操作。如果觸發器中的操作出了問題，會導致會員儲值金額更新失敗。我用下面的代碼演示一下：

mysql> update demo.membermaster set memberdeposit=20 where memberid = 2;
ERROR 1054 (42S22): Unknown column 'aa' in 'field list'

結果顯示，系統提示錯誤，欄位"aa"不存在。

這是因為，觸發器中的資料插入操作多了一個欄位，系統提示錯誤。可是，如果你不瞭解這個觸發器，很可能會認為是更新語句本身的問題，或者是會員資訊表的結構出了問題。說不定你還會給會員資訊表添加一個叫"aa"的欄位，試圖解決這個問題，結果只能是白費力。

2、相關資料的變更，可能會導致觸發器出錯。

特別是資料表結構的變更，都可能會導致觸發器出錯，進而影響資料操作的正常運行。這些都會由於觸發器本身的隱蔽性，影響到應用中錯誤原因排查的效率。

4.3 注意點

注意，如果在子表中定義了外鍵約束，並且外鍵指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此時修改父表被引用的鍵值或刪除父表被引用的記錄行時，也會引起子表的修改和刪除操作，此時基於子表的UPDATE和DELETE語句定義的觸發器並不會被啟動。

例如：基於子表員工表（t_employee）的DELETE語句定義了觸發器t1，而子表的部門編號（did）欄位定義了外鍵約束引用了父表部門表（t_department）的主鍵列部門編號（did），並且該外鍵加了"ON DELETE SET NULL"子句，那麼如果此時刪除父表部門表（t_department）在子表員工表（t_employee）有匹配記錄的部門記錄時，會引起子表員工表（t_employee）匹配記錄的部門編號（did）修改為NULL，但是此時不會啟動觸發器t1。只有直接對子表員工表（t_employee）執行DELETE語句時才會啟動觸發器t1。

上次修改於 2021-12-28