MySQL觸發器

一、觸發器初接觸

前段時間，在醫院上線專案，有個需求是：Java技術往表裡一直寫入資料，需要我將這些資料在存到另一個表中。當然，業務邏輯也能實現，但是想直接透過mysql來解決掉這個問題。於是就接觸到了觸發器…

二、觸發器含義

觸發器可以讓你在增、刪、改的時候執行一些特定的操作。可以在mysql中指定sql語句在執行前觸發或執行後觸發。

三、實現需求舉例

• 例如我上述的需求，當一個資料表中新增了資料後，就立馬同步到另一個表中。
• 當購買一個商品後，訂單表中新增一條資料，勢必會造成庫存減少。可用mysql觸發器來實現。
• 在寫入資料前，進行資料的校驗。

業務邏輯中的程式碼可以交予觸發器來實現，觸發器實現的也能交予業務邏輯來實現，具體是哪個更好，需要考慮業務邏輯、最佳化、簡便…

擴充套件文章：大型系統必須得要儲存過程和觸發器嗎？

四、觸發詳解

４.1基本語法：

delimiter 自定義結束符號
create trigger 觸發器名字 觸發時間 觸發事件 on 表 for each row
begin
    -- 觸發器內容主體，每行用分號結尾
end
自定義的結束符號
delimiter ;

關於on 表 for each：觸發物件，觸發器繫結的實質是表中的所有行，因此當每一行發生指定改變時，觸發器就會被觸發。

4.2 關於觸發事件

只要資料發生（增刪改）改變，就可以引起觸發事件。

• INSERT
• DELETE
• UPDATE

4.3 關於觸發時間

• before：在資料改變發生前來觸發。
• after：在資料改變發生後來觸發。

4.4 關於觸發物件

表中的每一行，表就是觸發物件。

4.5 關於new和old

觸發器針對的是資料庫中的每一行記錄，每行資料在操作前後都會有一個對應的狀態，觸發器將沒有操作之前的狀態儲存到 old 關鍵字中，將操作後的狀態儲存到 new 中

五、觸發器的優缺點

優點：

• 觸發器可以代替業務邏輯，減少客戶端和伺服器之間的通訊。
• 觸發器基於行觸發，具有事務的原子特性。
• 一個好的觸發器可以簡化很多應用邏輯。
• 可以對資料進行校驗。

缺點：

• 觸發器的出現的問題很難排查，增加資料庫的維護成本。
• 複雜邏輯會使觸發器邏輯層層巢狀，級聯關係追溯起來比較麻煩。
• 編碼中儲存過程的程式碼是明式呼叫，觸發器是隱式呼叫，不利於程式碼的維護與閱讀。
• 可能會造成死鎖和鎖等待，如果觸發器執行失敗，出現sql報錯，我們可能也不知道這是觸發器的原因。
• 可移植性差。

擴充套件文章：為什麼大家都不推薦使用MySQL觸發器而用儲存過程？

六、建立一個簡單的觸發器

現在有兩張空資料表：

• student表：

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | tinyint(255) | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| sex   | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

• stulog表：

mysql> desc stulog;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | tinyint(11) | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6.1 建立一個觸發器

該觸發器邏輯：往student表中插入（觸發條件）一條資料，插入後（觸發時間），自動把新資料中的name欄位給同步到stulog表中

mysql> delimiter ##
mysql> -- 建立觸發器
mysql> create trigger test after insert on student for each row
    -> begin
    ->     insert into stulog VALUES(null,new.name);
    -> end
    -> ##
Query OK, 0 rows affected (0.03 sec)

6.2 插入一條資料

mysql> insert into student (name,sex) values("光頭強","男");
Query OK, 1 row affected (0.01 sec)

6.3 檢視student中的資料

mysql> select * from student;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  1 | 光頭強    | 男   |
+----+-----------+------+
1 row in set (0.00 sec)

6.4 檢視stulog中的資料

mysql> select * from stulog;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 光頭強    |
+----+-----------+
1 row in set (0.00 sec)

發現已經將新插入的資料光頭強給同步過來了。

七、檢視一個觸發器

7.1 檢視所有觸發器

mysql> show triggers;

7.2 檢視某個觸發器的資訊

mysql> show create trigger test;

八、刪除修改一個觸發器

觸發器不能修改，只能刪除。

刪除一個表的同時，也會自動刪除該表上的觸發器。另外，觸發器不能更新或覆蓋，為了修改一個觸發器，必須先刪除它，再重新建立。

刪除命令：

mysql> drop trigger test;
Query OK, 0 rows affected (0.00 sec)

九、觸發器的原子性

• MYISAM：

如果含有觸發的表是MYISAM的，如果其中一個表報錯，另一個表也會正常執行。

• InooDb：

如果含有觸發的表是InooDb型別的，那麼一個表報錯，另一個表將會回滾。

十、總結

具體是否是使用觸發器，需要多方面的考量業務邏輯、硬體容量、併發等各方面因素。

---

參考文章：https://blog.csdn.net/babycan5/article/details/82789099 《高效能MySQL》

本作品採用《CC 協議》，轉載必須註明作者和本文連結