MSSQL 的觸發程序 TRIGGER
關於 TRIGGER 本篇將討論以下幾個問題
1. 什麼是 DML、DDL、DQL?
2. 什麼是 TRIGGER?
3. 如何使用 TRIGGER?
4. 情境
5. 使用建議
測試環境:
MS SQL:SQL Server 2019 Linux
SSMS:Microsoft SQL Server Management Studio 18
1. 什麼是 DML、DDL、DQL?
你可能沒有聽過 DML、DDL、DQL 這些縮寫,不過只要工作上有使用到 MSSQL,那就肯定使用過只是不知道這些縮寫罷了,說明如下
- DML(Data Manipulation Language):用於操作資料表內的資料(e.g. INSERT、UPDATE、DELETE)
- DDL(Data Definition Language):用於操作資料表(e.g. CREATE、ALTER、DROP)
- DQL(Data Query Language):用於資料查詢(e.g. SELECT)
本篇範例主要會說明如何建立在 DML 操作時會觸發的觸發程序(TRIGGER)。
2. 什麼是 TRIGGER?
MSDN 上說:「觸發程序是一種特殊的預存程序,其會在資料庫伺服器發生事件時自動執行。」
如同字面上的意思,在 INSERT、UPDATE、DELETE 等事件發生時,會觸發執行的程式。
3. 如何使用 TRIGGER?
本篇會以如何使用為主,實際運作細節會附上 MSDN 連結,有興趣的朋友可以深入研究。
- Create Trigger (MSDN)
- AFTER 觸發 (MSDN)
- if 條件中的 UPDATE() (MSDN)
- if 條件中的 EXIST() (MSDN)
4. 情境
- 資料表
UserInfo
中存有使用者資料,
- 現在有一筆資料的
DataCount
被異動了
- 希望藉由 Trigger 在 Update 事件發生時自動將異動的紀錄寫入
UserLog
資料表中
建立UserInfo
& UserLog
兩張資料表
手動在UserInfo
資料表中加入一筆資料
UserId |
UserName |
DataCount |
1 |
Wayne |
10 |
建立 Trigger UserInfo_UpdateTrigger
建立完成後可以在 SSMS 中資料表下的觸發程序中找到剛建立的 Trigger
右鍵可以開啟選單,進行修改、停用、刪除等操作
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
建立異動UserInfo
中DataCount
欄位的 Update
執行後可以看到兩次的「(1 個資料列受到影響)」
分別是 Insert UserLog & Update UserInfo 兩個操作
可以看到UserLog
中新增一筆異動前的紀錄
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
UserInfo
中DataCount
也更新為 20 了
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
5. 使用建議
雖然 Trigger 使用上很簡單,維護起來卻不是那麼容易,若是有多個複雜邏輯時,在除錯上就更是困難了,請謹慎評估後再使用。
另外建議不要在團隊不知情的情況下使用 Trigger,避免遇到問題時同事花了大把時間才發現原來資料寫入是在資料庫中觸發的,雖然 git 中不會有 commit 紀錄,使用前還是經由團隊開會後決議再使用比較保險。
總結
由於還是覺得 Trigger 維護上較不方便,我都只用在測試上,沒有在 Production 環境中使用過,所以是不是有什麼效能的坑或其他問題,也無法在此給各位建議。
參考資料
- Create Trigger MSDN
- AFTER 觸發 MSDN
- if 條件中的 UPDATE() MSDN
- if 條件中的 EXIST() MSDN
新手上路,若有錯誤還請告知,謝謝