[SQL]Trigger 撰寫時要注意的小細節
在兩年前開始整理 BLOG 的時候有想過有類似的一篇，但當時查一下 Google 大神，發現不少人都有針對 TRIGGER 寫了不少文章，因此自己就把資料整理一下而沒有去寫了。最近剛好看到一篇網路上的文章，看到作者的範例碼，又遇到幾個同事有遇到類似的問題，因此就想說當成說明文件整理一下，以後遇到有朋友有問題的時候，再請他們來參考一下囉。
在開始之前，我先建立一個範例的測試資料表，等一下會用這個資料表來作範例
-- 建立範例資料表
CREATE TABLE [Sample1]
(
A1 INT PRIMARY KEY,
A2 VARCHAR(10),
A3 DATETIME DEFAULT (GETDATE())
)
GO
-- 放一些測試用資料
INSERT INTO [Sample1] ( A1,A2 ) VALUES
( 1, 'A'), ( 2, 'B'), ( 3, 'C'), ( 4, 'D'), ( 5, 'E');
GO
-- 查看資料是否有五筆資料正確存入
SELECT * FROM [Sample1]
GO
另外不免俗的要做一個 Log 檔案，當有 Trigger 發生的時候，可以讓我們來紀錄資料的變化，因此這裡我先建立一個 Table
CREATE TABLE [ActionLog]
(
L1 VARCHAR(10),
L2 DATETIME DEFAULT (GETDATE()),
A1 INT,
A2 VARCHAR(10),
A3 DATETIME
)
GO
在 SQL Server 的 DML Trigger 有好幾種，我們目前先針對定義在 Table 上面的 AFTER ( DML Trigger 可以分 AFTER 和 INSTEAD OF 這兩種 Triggers )，因此撰寫的時候要指定是 [ DELETE ] , [ INSERT ] , [ UPDATE ] 的哪種狀態下要去觸發的，一般我個人的習慣是建議把這三種分開去寫三個，這樣當有問題的時候你可以一個一個 DISBALE，比較不容易混淆。假設在這裡我先寫一個最常見的，就是在原始 TABLE ( Sample1 ) 的資料表做刪除，然後把資料放到 ActionLog 的資料表內，因此在網路上可以找到很多這樣的範例，可能會寫成類似下列的寫法：
-- 在 [dbo].[Sample1] 上建立一個 [TRG_Sample1_DELETE] ,當刪除資料時候會觸發
CREATE TRIGGER [TRG_Sample1_DELETE] ON [dbo].[Sample1]
AFTER DELETE
AS
DECLARE @A1 INT;
DECLARE @A2 VARCHAR(10);
DECLARE @A3 DATETIME;
-- 取出刪除紀錄
SELECT @A1=A1,@A2=A2,@A3=A3 FROM deleted;
-- 存到 LOG 檔案內
INSERT INTO [ActionLog] ( L1,A1,A2,A3 ) VALUES ( 'DELETE', @A1, @A2, @A3) ;
-- 畫面上顯示確認 TRIGGER 有執行
PRINT GETDATE();
GO
這樣我們來測試一下
-- 刪除一筆資料
DELETE FROM [dbo].[Sample1] WHERE A1 = 1;
-- 查看範例資料表內資料是否刪除
SELECT * FROM [dbo].[Sample1];
-- 查看 LOG 檔案內是否有紀錄資料
SELECT * FROM [dbo].[ActionLog];
結果如下：
結果看起來似乎很正常，紅字 1 的地方表示 TRIGGER 有正常運作，所以會因為有一行 PRINT 而顯示異動時間；紅字 2 的地方表示刪除命令完成，所以那個是刪除資料的結果，因為受到 TRIGGER 的回傳值得影響，因此會顯示一個異動時間；紅字 3 的地方看到原始資料內已經少一筆紀錄了；而紅字 4 的地方可以看到會真實記錄異動資料到我們希望的資料表內。這樣看起來似乎都很正常，但不知道眼尖的各位，是否會感覺到有些壞味道在這個範例當中，雖然這個案例是可以正常執行的，但似乎有一些不是很合適的作法。
“請問當我們在使用 DML 指令的時候， 如果一次異動超過一筆紀錄的時候，同一個 TRIGGER 將會被觸發幾次 ? " 首先我們先要注意一件事情，這也是我們時常在教開發人員的時候特別要去提醒的，在 SQL Server 的 Trigger 如果沒有發生遞迴的狀況下，正常來說應該只會發生一次 ( 這點跟 Oracle 有點不同 )，因此上述的 Trigger 範例中，如果我們異動的資料超過兩筆的時候，因為我們在中間會有一段將欄位資料取出來放到變數內，在這裡可能就會發生一些問題。所以接下來我們做個測試，刪除其中的三筆資料，看會發生甚麼樣的結果
-- 刪除三筆資料
DELETE FROM Sample1 WHERE A1 < 5
-- 查看範例資料表
SELECT * FROM Sample1
-- 查看 LOG 檔內的紀錄
SELECT * FROM ActionLog
結果如下：
從紅字 5 的地方可以看出，資料刪除 3 筆之後，在範例資料表內只剩下 1 筆紀錄；但是在紅字 6 的地方可以看出來，在我們之前撰寫 DELETE Trigger 的時候，因為先把欄位值存到變數內，因此當我們取的時候有多筆紀錄的時候，他會抓到刪除資料內的第一筆的異動紀錄，然後將結果存起來，因此在撰寫 Trigger 的時候要特別注意一下，在 inserted 和 deleted 這兩個特殊的資料表，有可能存放的不只一筆紀錄。因此如果你只是要做紀錄或者是做一些計算，最好是直接針對 inserted 和 deleted 直接來做處理，非不得已的狀況下，儘量不要去使用 cursor 來做。以上述的例子，或許可以改成以下的寫法。
ALTER TRIGGER [dbo].[TRG_Sample1_DELETE] ON [dbo].[Sample1]
AFTER DELETE
AS
-- 存到 LOG 檔案內
INSERT INTO [ActionLog] ( L1,A1,A2,A3 )
SELECT 'DELETE', A1, A2, A3 FROM deleted ;
-- 畫面上顯示確認 TRIGGER 有執行
PRINT GETDATE();
接著我們再做一次實驗，看是否會順利完成我們所希望的結果
-- 再新增兩筆資料
INSERT INTO Sample1(A1,A2) VALUES
( '1','AA' ), ( '2','BB' );
-- 刪除兩筆資料
DELETE FROM Sample1 WHERE A1 < 5;
-- 查看 LOG 檔內的紀錄
SELECT * FROM ActionLog;
結果如下：
這樣雖然告一個段落，但是在這段的處理中還有幾個要注意的地方。延續上面的例子，當我們有資料被刪除的時候，因為我們有設定資料表的 DELETE Trigger，所以就會執行相關的處理，但要是我們下 DELETE 的指令，但「沒有刪除任何存在的記錄」的時候，那 Trigger 會被啟動嗎 ? 這也是我們在課堂上學員常會搞混的地方，我們直接再來 Demo 一次。
-- 查看範例資料表
SELECT * FROM Sample1;
-- 沒有任何符合條件的資料被刪除
DELETE FROM Sample1 WHERE A1 > 5;
-- 查看 LOG 檔內的紀錄
SELECT * FROM ActionLog;
結果如下：
可以從紅色框框中的訊息可以看出，當我們 DELETE 的時候，雖然沒有刪除到任何的資料，但是 Trigger 仍然是會被執行的，只是因為 deleted 內的資料集沒有任何資料，因此當我們用 「INSERT INTO …. SELECT 欄位 FROM deleted」 去做紀錄的時候，就沒有產生任何的資料到我們的 ActionLog 的 Log 檔案內了。
除了上述被觸發的時機、次數和資料筆數之外，還有一個比較容易忽略的問題，就是「多回傳一次所影響的資料」，這個看起來似乎沒有甚麼太大的影響，感覺上似乎只是稍微影響回傳執行結果的時候，多傳了一些資訊而已。但是在某些程式語言在開發資料庫應用程式的時候，因為可能不是直接下 SQL 去變更資料，有可能是利用 ADO 的 Recordset 來做處理的，他會去接收 DML 語法所變更的資料數，但此時因為有 Trigger 也有回傳，可能會造成程式上會有所誤判，因此比較好的寫法，是在 Trigger 一開始的地方，就加上「SET NOCOUNT ON」，就可以避開這類的問題了。
因此語法可能會改成類似以下的處理
ALTER TRIGGER [dbo].[TRG_Sample1_DELETE] ON [dbo].[Sample1]
AFTER DELETE
AS
SET NOCOUNT ON;
-- 存到 LOG 檔案內
INSERT INTO [ActionLog] ( L1,A1,A2,A3 )
SELECT 'DELETE', A1, A2, A3 FROM deleted ;
-- 畫面上顯示確認 TRIGGER 有執行
PRINT GETDATE();
這樣當我們有去刪除資料的時候，就不會多接收到 Trigger 內所影響的資料樹了。
-- 沒有任何符合條件的資料被刪除
DELETE FROM Sample1 WHERE A1 = 5;
-- 查看 LOG 檔內的紀錄
SELECT * FROM ActionLog;
結果如下：
如果前面的範例，一般來說當我們使用 DML 的指令的時候，一次只會觸發一種類型的 Trigger。但從 SQL Server 2008 之後，SQL Server 多了一個新的語法，可以使用 MERGE 同時完成插入、更新，和刪除資料，在對於我們資料處理上可以減少多次的往返。但如果我們使用 MERGE 的時候，Trigger 是否會被觸發呢 ? 那會被觸發幾次呢 ? 這裡我們就實地的來做一個範例，測試一下囉。
首先我們還是一樣先建立測試資料表和放一些測試用資料
-- 建立範例資料表
CREATE TABLE [Sample2]
(
A1 INT PRIMARY KEY,
A2 VARCHAR(10),
A3 DATETIME DEFAULT (GETDATE())
)
GO
-- 建立 Merge 動作時 Trigger 發生的 Log
CREATE TABLE [MergeLog]
(
L1 VARCHAR(10),
L2 DATETIME DEFAULT (GETDATE()),
A1 INT,
A2 VARCHAR(10),
A3 DATETIME
)
GO
-- 放一些測試用資料
INSERT INTO [Sample2] ( A1,A2 ) VALUES
( 1, 'A'), ( 2, 'B'), ( 3, 'C'), ( 4, 'D'), ( 5, 'E');
GO
-- 查看資料是否有正確存入
SELECT * FROM [Sample2]
GO
接著我們在測試資料表 Sample2 上面分別建立三組 Trigger，但這三組稍微跟前面範例的有些許不同，因為怕 Trigger 發生得太快，後續沒有辦法區分執行的先後關係，因此在每個 Trigger 的最後，都讓他在處理完畢之後等待兩秒鐘，這樣後續我們從 Log 中就比較容易區分先後順序的關係了，因此三組 Trigger 的內容如下
-- Insert Triggrt
CREATE TRIGGER [dbo].[TRG_Sample2_INSERT] ON [dbo].[Sample2]
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO [MergeLog] ( L1,A1,A2,A3 )
SELECT 'INSERT', A1, A2, A3 FROM inserted ;
WAITFOR DELAY '00:00:02';
GO
-- Delete Trigger
CREATE TRIGGER [dbo].[TRG_Sample2_DELETE] ON [dbo].[Sample2]
AFTER DELETE
AS
SET NOCOUNT ON;
INSERT INTO [MergeLog] ( L1,A1,A2,A3 )
SELECT 'DELETE', A1, A2, A3 FROM deleted ;
WAITFOR DELAY '00:00:02';
GO
--Update Trigger
CREATE TRIGGER [dbo].[TRG_Sample2_UPDATE] ON [dbo].[Sample2]
AFTER UPDATE
AS
SET NOCOUNT ON;
INSERT INTO [MergeLog] ( L1,A1,A2,A3 )
SELECT 'UPDATE-Ins', A1, A2, A3 FROM inserted
UNION ALL
SELECT 'UPDATE-Del', A1, A2, A3 FROM deleted
WAITFOR DELAY '00:00:02';
GO
為了要測試，我們在下面的程式碼中故意把資料交錯，編號 1 和 3 的做更新，2 和 4 做刪除，最後 6、7、8 最新增。正常來說這個指令應該會做得很快，但因為我們在 Trigger 內都有加入等待兩秒鐘的處理，因此這裡可能會稍微等一下。
MERGE Sample2 AS Target
USING ( VALUES (1,'AAA'), (2, NULL), (3, 'CCC'), (4, NULL),
(6,'XXX'), (7,'YYY'), (8, 'ZZZ')
) AS Source (S1, S2)
ON (Target.A1 = Source.S1)
WHEN MATCHED AND Source.S2 IS NULL
THEN DELETE
WHEN MATCHED
THEN UPDATE SET Target.A2 = Source.S2
WHEN NOT MATCHED BY TARGET THEN
INSERT (A1, A2)
VALUES (Source.S1, Source.S2);
GO
最後我們查看一下 MergeLog 資料表內的內容，看來 Merge 指令當要處理資料的時候，會先做 INSERT，其次是 UPDATE，最後做 DELETE。
Trigger 是我們在資料庫內很常用的一種技術，相信透過前面的說明，應該可以讓大家在使用的時候可以更加清楚，如果有興趣的朋友，也可以在文章下方留言，我們再來討論看看囉。