五餅二魚工作室

前一陣子朋友發了一個訊息來詢問有關於 Trigger 的問題，朋友表示他已經先看過「Trigger 撰寫時要注意的小細節」、「TRIGGER 使用時的權限問題」，但都沒有辦法解決他的問題，因此將他一大段很長的 Trigger 傳給我看，要我協助查看為什麼他在 Trigger 內，有時會遺漏寫入到自己 Log 的資料表的資料。

看了一下那段很長的 Trigger 之後，大概知道他發生異常的原因了，因此想說再整理一下，免得自己下次又遇到類似的狀況。

首先朋友的資料表我將他簡化一下，大概會類似以下的狀況：

-- 測試用資料表
CREATE TABLE [dbo].[T1](
	[F1] [int] NULL,
	[F2] [nvarchar](10) NULL
) ON [PRIMARY]
GO

-- 用來存放處理過程的 Log 資料表
CREATE TABLE [dbo].[DBLog](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[LogMessage] [nvarchar](512) NULL,
	[LogTime] [datetime] NULL DEFAULT GETDATE()
) ON [PRIMARY]
GO

因此他希望在 T1 資料發生 INSERT 的時候，會去做一些相關的計算和處理，並且把一些處理狀況記錄到 DBLog 的資料表，因此他的 Trigger 會類似以下的寫法

CREATE  TRIGGER [dbo].[TRIG_T1_INSERT] ON [dbo].[T1] AFTER INSERT
AS
	SET NOCOUNT ON	;
	BEGIN TRY
		INSERT INTO DBLog(LogMessage) VALUES ( 'TRIG_T1_INSERT Log Start');
		-- 處理相關資料
		-- INSERT XXXX 
		-- UPDATE YYYY
		INSERT INTO DBLog(LogMessage) VALUES ( 'TRIG_T1_INSERT Log Finish');
	END TRY
	BEGIN CATCH
		INSERT INTO DBLog(LogMessage) VALUES ( 'TRIG_T1_INSERT ERROR');
	END CATCH
GO

因此當我們進行測試的時候，看起來一切都很正常

INSERT INTO T1(F1,F2) VALUES ( 1, 'AAAAA')
GO

SELECT * FROM T1;

SELECT * FROM DBLog;

---

為了要模擬 Trigger 內的指令發生錯誤，因此我們調整一下 Trigger，讓他故意會發生異常，因此我們修改原本的 Trigger ，故意讓資料塞入 10 的時候會發生錯誤

ALTER  TRIGGER [dbo].[TRIG_T1_INSERT] ON [dbo].[T1] AFTER INSERT
AS
	SET NOCOUNT ON	;
	BEGIN TRY
		INSERT INTO DBLog(LogMessage) VALUES ( 'TRIG_T1_INSERT Log Start');
		-- 故意讓 F1 資料為 10 的時候會發生錯誤
		SELECT 1/(10-F1) FROM inserted ;
		-- 
		INSERT INTO DBLog(LogMessage) VALUES ( 'TRIG_T1_INSERT Log Finish');
	END TRY
	BEGIN CATCH
		INSERT INTO DBLog(LogMessage) VALUES ( 'TRIG_T1_INSERT ERROR');
	END CATCH
GO

此時我們重新測試一下，從下圖中可以看出，我們在塞入 9 , 10 , 11 三筆資料的時候， 10 的那筆資料真的發生錯誤了

而當我們看資料表內的資料，可以看到 9 和 11 這兩筆資料填入的時候， DBLog 資料表內也有紀錄到，但是 10 那一筆的資料不只沒有寫入到 T1 的資料表，且 DBLog 內也因為 Trigger 內發生錯誤，整個 Rollback 掉寫入的資料，所以就沒有正常寫入了。

但如果我們希望能把相關的錯誤記錄還是可以寫入到 DBLog 的資料表內，那麼在原本的 Trigger 內就要做一些調整，在 BAGIN CATCH 進行異常處理的時候，多加入 XACT_STATE() 函數的判斷

ALTER  TRIGGER [dbo].[TRIG_T1_INSERT] ON [dbo].[T1] AFTER INSERT
AS
	SET NOCOUNT ON	;
	BEGIN TRY
		INSERT INTO DBLog(LogMessage) VALUES ( 'TRIG_T1_INSERT Log Start');
		-- 故意讓 F1 資料為 10 的時候會發生錯誤
		SELECT 1/(10-F1) FROM inserted ;
		-- 
		INSERT INTO DBLog(LogMessage) VALUES ( 'TRIG_T1_INSERT Log Finish');
	END TRY
	BEGIN CATCH
		-- 多增加這一行判斷，讓交易先被 ROLLBACK 掉，這樣才能正常寫入		
		IF (XACT_STATE()) = -1 ROLLBACK ;
		INSERT INTO DBLog(LogMessage) VALUES ( 'TRIG_T1_INSERT ERROR');
	END CATCH
GO

此時我們再進行測試，依然填入 10 的資料讓交易發生錯誤，從下圖中可以看到，INSERT  T1 資料表發生錯誤，我們在資料表內沒有辦法塞入 10 的資料，但是在 DBLog 內，就可以正常紀錄了。

一般來說我們儘量不要在 Trigger 內進行複雜的資料處理，免得發生異常並且拖長交易的時間，但如果不能避免需要使用的時候，則要記得搭配 TRY ... CATCH 的處理，並且可以搭配 XACT_STATE() 來判斷交易狀況，完整地將您所需要記錄的資料寫入到您自己的紀錄檔案內。