[MSSQL] Trigger 觸發程序，記錄某資料表所有異動 @ 黃昏的甘蔗

201612271410[MSSQL] Trigger 觸發程序，記錄某資料表所有異動

前一篇「Trigger 觸發程序初體驗 - 當某資料表被異動時自動記錄」寫了怎麼完成觸發的動作，因為阿裡阿雜的亂寫一堆，所以把後來的整段 T-SQL 挪到這篇來講。可以使用底下的 TSQL 簡單的利用 Trigger 在SQL 層級記錄某資料表所有的異動。

底下例子假設要監看的
資料庫為 test
資料表為 Account
--- TSQL 產出 ---------------------------
備份異動的資料表 Tr_Account 所有異動會被記錄在此
觸發程序 Tr_Account_changed

-- 建立監看某Table 的Trigger 機制，任何異動都會被記錄在Tr_table 中

USE test;                       -- 將test 改成你要監看的資料庫名稱
GO                               -- 執行
DECLARE @Source_Table varchar(100) = 'Account';   -- 將Account 改成你要監看的資料表
DECLARE @Tr_Table varchar(105) = 'Tr_Account' ;
DECLARE @TriggerName varchar(115)= 'Tr_Account_changed';
-- select @Source_Table,@Tr_Table,@TriggerName;
-- 複製資料表，建立trigger 觸發的記錄表
--select * datetime into @Tr_Table from @Source_Table -- 完全複製
-- select * into @Tr_Table from @Source_Table where 1=0 -- 只複製Table schema

DECLARE @log_time char(19) = CONVERT(char(19), getdate(), 120); -- format 120 格式是西元年-月-日時:分.秒.微秒
DECLARE @action_type varchar(20)='';   -- 初始化在 action type 留空，
IF EXISTS(SELECT TOP 1 1 from Tr_Account)
SELECT '資料表Tr_Account 已存在，不再建立' -- 要注意當原 Table Scehma 有改變時，會導致後續寫入失敗，以 FOR 的作法，正常的資料表寫入也會被截斷，可改用 AFTER
ELSE                              -- 增加兩個欄位分別記錄異動狀態，異動日期時間
SELECT @action_type AS action_type ,*, @log_time AS log_time into Tr_Account  from Account ;
GO
DROP TRIGGER Tr_Account_changed; -- 刪除原本同名trigger
GO
CREATE TRIGGER Tr_Account_changed  ON Account   -- 建立Trigger，觸發程序名稱Tr_Account_changed 作用在資料表Account
FOR UPDATE,INSERT,DELETE
AS
BEGIN
DECLARE @log_time char(19) = CONVERT(char(19), getdate(), 120);
IF EXISTS(SELECT top 1 1 from inserted) AND EXISTS(SELECT top 1 1 from deleted) -- Update
    BEGIN
      INSERT INTO Tr_Account  SELECT 'UPDATE_del',*,@log_time FROM deleted;
      INSERT INTO Tr_Account  SELECT 'UPDATE_ins',*,@log_time FROM inserted;
    END
ELSE
    BEGIN
      IF EXISTS(SELECT top 1 1 from inserted)
        INSERT INTO Tr_Account  SELECT 'INSERT',*,@log_time FROM inserted;   -- Insert
      IF EXISTS(SELECT top 1 1 from deleted)
        INSERT INTO Tr_Account  SELECT 'DELETE',*,@log_time FROM deleted;    -- Delete
    END
END
-- inserted 代表 insert 的資料或 update 「後」的資料
-- deleted 代表 delete 的資料或 update 「前」的資料

-- Trigger 執行到此即可 ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑

-- 查看Trigger -------
SELECT OBJECT_NAME(t.parent_id) AS 資料表, t.name as 觸發名稱,te.type_desc AS 觸發事件 ,
t.object_id AS Object_ID, t.create_date AS 建立日期, t.modify_date AS 修改日期
FROM sys.triggers AS t INNER JOIN sys.trigger_events AS te ON t.object_id = te.object_id
INNER JOIN sys.tables tb ON t.parent_id = tb.object_id