[SQL]如何在 Trigger 中異常時需要將錯誤寫入到資料庫內 ?

當資料異動發生 Trigger 的時候,正常來說如果有異常發生,則所有寫入到資料表的資料會全部 ROLLBACK,該怎麼避開而能順利將錯誤資訊寫入到資料表內呢 ?

前一陣子朋友發了一個訊息來詢問有關於 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() 來判斷交易狀況,完整地將您所需要記錄的資料寫入到您自己的紀錄檔案內。