一、觸發器初接觸
前段時間,在醫院上線專案,有個需求是:Java技術往表裡一直寫入資料,需要我將這些資料在存到另一個表中。當然,業務邏輯也能實現,但是想直接透過mysql來解決掉這個問題。於是就接觸到了觸發器…
二、觸發器含義
觸發器可以讓你在增、刪、改的時候執行一些特定的操作。可以在mysql中指定sql語句在執行前觸發或執行後觸發。
三、實現需求舉例
- 例如我上述的需求,當一個資料表中新增了資料後,就立馬同步到另一個表中。
- 當購買一個商品後,訂單表中新增一條資料,勢必會造成庫存減少。可用mysql觸發器來實現。
- 在寫入資料前,進行資料的校驗。
業務邏輯中的程式碼可以交予觸發器來實現,觸發器實現的也能交予業務邏輯來實現,具體是哪個更好,需要考慮業務邏輯、最佳化、簡便…
擴充套件文章:大型系統必須得要儲存過程和觸發器嗎?
四、觸發詳解
4.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 中
觸發器事件 | new和old |
---|---|
INSERT | 沒有 old,只有 new,new 表示將要(插入前)或者已經增加(插入後)的資料 |
DELETE | 既有 old 也有 new,old 表示更新之前的資料,new 表示更新之後的資料 |
UPDATE | 沒有 new,只有 old,old 表示將要(刪除前)或者已經被刪除(刪除後)的資料 |
五、觸發器的優缺點
優點:
- 觸發器可以代替業務邏輯,減少客戶端和伺服器之間的通訊。
- 觸發器基於行觸發,具有事務的原子特性。
- 一個好的觸發器可以簡化很多應用邏輯。
- 可以對資料進行校驗。
缺點:
- 觸發器的出現的問題很難排查,增加資料庫的維護成本。
- 複雜邏輯會使觸發器邏輯層層巢狀,級聯關係追溯起來比較麻煩。
- 編碼中儲存過程的程式碼是明式呼叫,觸發器是隱式呼叫,不利於程式碼的維護與閱讀。
- 可能會造成死鎖和鎖等待,如果觸發器執行失敗,出現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 協議》,轉載必須註明作者和本文連結