如何利用DML Trigger進行資料異動的Log
Trigger（觸發程序）是一種SQL Server用來回應事件觸發時會自動執行的預存程序（其詳細語法如下），SQL Server提供下列三種Trigger：
- DML Trigger
- DDL Trigger
- 登入 Trigger
在SQL Server 2005（含以上）的版本新增DML Trigger，提供After Trigger與Instead Of Trigger兩種不同的機制，讓開發人員或DBA可以選擇符合其商業環境所需的Trigger來滿足需求，前者觸發於對Table進行Insert、Update及Delete的SQL敘述成功執行時；後者可用Instead Of Trigger來覆寫原本要執行的SQL敘述，Instead Of Trigger可以建立於Table或View，讓View可以支援更新。
本文先將重點放在After Trigger，有關Instead Of Trigger將來有機會再來討論。如上述，After Trigger會在對Table進行Insert、Update及Delete等動作時被觸發，因此無法在View上建立After Trigger，除Select外每項對Table的操作都會將異動的資料進行暫存，Insert動作引發Trigger時，會將新增的資料暫存於inserted table；Delete動作觸發Trigger時，會將刪除的資料暫存於deleted table；Update動作引發Trigger時，會將修改前的資料暫存於deleted table，修改後的資料暫存於inserted table，接下來開始進行DML Trigger的撰寫。
下圖有兩個table，其中Contractors為前端AP所存取的主要對象，我們將分別建立Insert、Update及Delete動作的After Trigger，以記錄ContractorsLog前端AP對Contractors異動的所有經過。
- 下列程式碼示範如何建立Insert After Trigger，於Insert資料至Contractors時觸發，將新增的資料留一份至ContractorsLog。
1: IF OBJECT_ID ('dbo.trg_Contractors_Insert','TR') IS NOT NULL
2: DROP TRIGGER dbo.trg_Contractors_Insert
3: GO
4:
5: CREATE TRIGGER dbo.trg_Contractors_Insert ON Contractors
6: AFTER INSERT
7: AS
8: INSERT INTO ContractorsLog
9: ([BrokerId]
10: ,[Name]
11: ,[Title]
12: ,[PhoneNumber]
13: ,[EMail]
14: ,[Action]
15: ,[UpdateDate])
16: SELECT [BrokerId]
17: ,[Name]
18: ,[Title]
19: ,[PhoneNumber]
20: ,[EMail]
21: ,'I'
22: ,[UpdateDate]
23: FROM INSERTED
24: GO
- 下列程式碼示範如何建立Delete After Trigger，於Delete Contractors中的資料時觸發，將刪除的資料留一份至ContractorsLog。
1: IF OBJECT_ID ('dbo.trg_Contractors_Delete','TR') IS NOT NULL
2: DROP TRIGGER dbo.trg_Contractors_Delete
3: GO
4:
5: CREATE TRIGGER dbo.trg_Contractors_Delete ON Contractors
6: AFTER DELETE
7: AS
8: INSERT INTO ContractorsLog
9: ([BrokerId]
10: ,[Name]
11: ,[Title]
12: ,[PhoneNumber]
13: ,[EMail]
14: ,[Action]
15: ,[UpdateDate])
16: SELECT [BrokerId]
17: ,[Name]
18: ,[Title]
19: ,[PhoneNumber]
20: ,[EMail]
21: ,'D'
22: ,[UpdateDate]
23: FROM DELETED
24: GO
- 下列程式碼示範如何建立Update After Trigger，於Update Contractors中的資料時觸發，將修改前後的資料各留一份至ContractorsLog。
1: IF OBJECT_ID ('dbo.trg_Contractors_Update','TR') IS NOT NULL
2: DROP TRIGGER dbo.trg_Contractors_Update
3: GO
4:
5: CREATE TRIGGER dbo.trg_Contractors_Update ON Contractors
6: AFTER UPDATE
7: AS
8: INSERT INTO ContractorsLog
9: ([BrokerId]
10: ,[Name]
11: ,[Title]
12: ,[PhoneNumber]
13: ,[EMail]
14: ,[Action]
15: ,[UpdateDate])
16: SELECT [BrokerId]
17: ,[Name]
18: ,[Title]
19: ,[PhoneNumber]
20: ,[EMail]
21: ,'d'
22: ,[UpdateDate]
23: FROM DELETED
24:
25: INSERT INTO ContractorsLog
26: ([BrokerId]
27: ,[Name]
28: ,[Title]
29: ,[PhoneNumber]
30: ,[EMail]
31: ,[Action]
32: ,[UpdateDate])
33: SELECT [BrokerId]
34: ,[Name]
35: ,[Title]
36: ,[PhoneNumber]
37: ,[EMail]
38: ,'i'
39: ,[UpdateDate]
40: FROM INSERTED
41: GO
測試After Trigger的方式很簡單，只要分別對Contractors table進行Insert、Update及Delete即可，結果如下：
- Insert
- Update
- Delete
參考資料：
http://msdn.microsoft.com/zh-tw/library/ms189799.aspx