[SQL Server] Trigger的資料異動紀錄(part2)

有時候我們會在資料表都建立後, 才想到要另外加個欄位(UpdateTime)記錄修改時間. 但欄位建了, 程式卻可能因為種種原因無法配合修改. 所以此時可透過 trigger 來幫助完成這項功能.
不過, 假如在 TableA 中加入了一個 AFTER UPDATE 的 trigger(TableA_UptTrigger) 去修改 TableA 的資料, 很顯然地可能會導致迴圈的產生. 此時, 有以下兩種選擇:

1. 將 trigger 的遞迴功能關閉:

   ALTER DATABASE databasename
   SET RECURSIVE_TRIGGERS OFF
   

   這個設定是針對整個資料庫做的設定, 所以如果本身寫 trigger 的經驗不多, 先關掉也許也保險一點.
2. 在 RECURSIVE_TRIGGERS 是 ON 的狀態下, 透過 TRIGGER_NESTLEVEL() 這個函式來避免無窮迴圈. 例如以下的例子:

   IF TRIGGER_NESTLEVEL()<2 
   BEGIN
     UPDATE TableA SET UpdateTime=getdate()
     FROM INSERTED I
     WHERE I.Seq=TableA.Seq
   END
   

如果你的 TableA 修改後, 還要寫一個紀錄到例如 TableA_Log 的資料表. 你可能會發現, TableA_Log 所紀錄的 UpdateTime 欄位是舊的, 因為你是在 Update 的 trigger 中才去修改 UpdateTime 的欄位資料. 此時你可以透過一個 @uptTime 變數去設定 UpdateTime 的值, 並將這個值寫至 log, 例如:

Declare @uptTime datetime
Set @uptTime=getdate()
UPDATE TableA SET UpdateTime=@uptTime
FROM INSERTED I
WHERE I.Seq=TableA.Seq
-- 這邊的 Log 表紀錄的是新資料(INSERTED)
INSERT INTO TableA_Log(Seq, Status, UpdateTime)
SELECT I.Seq, I.Status, @uptTime
FROM INSERTED I

若是你不止要修改 UpdateTime, 還有一些 Status 要改(ex: Status 的修改規則為舊的值若為 C 則改成 U, 其餘照新值設定), 可以參考以下的例子:

Declare @uptTime datetime
Set @uptTime=getdate()
UPDATE TableA SET UpdateTime=@uptTime
, Status=CASE WHEN D.Seq='C' THEN 'U' ELSE I.Status END
FROM INSERTED I INNER JOIN DELETED D ON I.Seq=D.Seq
WHERE I.Seq=TableA.Seq
-- 這邊的 Log 表紀錄的是新資料(INSERTED)
INSERT INTO TableA_Log(Seq, Status, UpdateTime)
SELECT I.Seq, CASE WHEN D.Seq='C' THEN 'U' ELSE I.Status END, @uptTime
FROM INSERTED I INNER JOIN DELETED D ON I.Seq=D.Seq

原則上還是建議不要讓 trigger 做太複雜或危險的事(ex:迴圈), 以避免將來開發上多了一塊工程師常遺漏的黑色地帶.