2018年9月11日 星期二

利用 Trigger 紀錄資料表異動 (Log)


原始資料表：[xxx]
異動資料表：[xxx_log]

[xxx_log] 比 [xxx] 至少要多兩個欄位：

  • T1，異動時間，Datetime，預設 getdate() 即可。
  • T2，異動指令，Varchar(10)，存放 INSERT UPDATE DELETE 做為識別。

2019/03/25 補充
兩個系統資料表 [INSERTED] & [DELETED]
執行指令 insert 後，[INSERTED] 表內有新增的資料，[DELETED] 表內無資料。
執行指令 delete 後，[INSERTED] 表內無資料，[DELETED] 表內有刪除的資料。
執行指令 update 後，[INSERTED] 表內有更新後的資料，[DELETED] 表內有更新前的資料。
所以執行指令後，可藉由 select 兩個表判斷為哪種行為，並取得資料。
 
建立一個名為 tr_xxx 的 Trigger

CREATE TRIGGER tr_xxx ON [dbo].[xxx] --// 在資料表 xxx 建立一個名為 tr_xxx 的 Trigger
AFTER INSERT, UPDATE --// 如果需要判斷 DELETE 得加進來
AS

SET NOCOUNT ON; --// 這行是為了正確紀錄批次 UPDATE 或 DELETE，如果不寫，只會 LOG 到一筆異動
DECLARE @INS int, @DEL int --// 取得 INSERTED 與 DELETED 的資料數

--// INSERT：@INS > 0 AND @DEL = 0
--// DELETE：@INS = 0 AND @DEL > 0
--// UPDATE：@INS > 0 AND @DEL > 0

SELECT @INS = COUNT(*) FROM INSERTED
SELECT @DEL = COUNT(*) FROM DELETED

IF @INS > 0 AND @DEL > 0 
BEGIN
     INSERT INTO [xxx_log]  ( T2, C1, C2, C3 ... )  
         SELECT 'UPDATE', C1, C2, C3 ... FROM INSERTED
END

ELSE 
BEGIN
     INSERT INTO [xxx_log]  ( T2, C1, C2, C3 ... )  
         SELECT 'INSERT', C1, C2, C3 ... FROM INSERTED
END

參考資料：
https://stackoverflow.com/questions/9931839/create-a-trigger-that-inserts-values-into-a-new-table-when-a-column-is-updated
https://dotblogs.com.tw/jamesfu/2014/06/20/triggersample
標籤： , ,