[SQL Server] 利用 Trigger 進行資料異動的備份

在系統開發過程, 我們往往沒事先規劃在一些重要資料修改過程中進行備份. 使得系統上線後又要改程式, 將程式中有進行修改 / 刪除的行為, 加入備份的程式碼 (例如將欲修改的資料寫一份到 log 表格). 但也許在經過工程師來來去去後, 新的工程師又忘了加入備份的程式碼, 導致最後又要重新檢視所有程式進行修改.
為了避免上述的情況不段重演, 所以考慮在不動到程式的情況下進行資料備份, 也就是利用 trigger, 在指定表格修改或刪除的時候將資料備一份到 Log 表格.
以下我使用一個簡單的會員表格 (UserAccount) 進行說明:

1. 建立 UserAccount 表格, 並新增幾筆資料以進行測試.

   Column Name	Data Type	Description
   user_name	varchar(50)	帳號(PK)
   user_pw	varchar(50)	密碼
   user_email	varchar(100)	電子信箱
   user_language	varchar(3)	ENU, CHT, etc
   user_loginTime	datetime	最近登入日期

   
   測試資料如下圖:
   
2. 建立一個 Log 資料表, 此表格僅比 UserAccount 表格多了三個欄位, 分別是 logId, logTime, logMode, 說明如下:

   Column Name	Data Type	Description
   logId	bigint	(PK) 為避免資料成長超過預期, 所以用 bigint
   user_name	varchar(50)	帳號
   user_pw	varchar(50)	密碼
   user_email	varchar(100)	電子信箱
   user_language	varchar(3)	ENU, CHT, etc
   user_loginTime	datetime	最近登入日期
   logTime	datetime	寫入 log 的時間, 預設值為 getdate(),
   logMode	char(1)	U: 修改, D: 刪除

3. 接下來就開始撰寫 trigger. CREATE TRIGGER 的 Script 如下:

   CREATE TRIGGER dbo.Tr_UserAccount_Change ON dbo.UserAccount
   AFTER UPDATE,DELETE
   AS
   
   INSERT INTO dbo.UserAccount_Log (user_name, user_pw, user_email, user_language, user_loginTime, logMode)
   SELECT d.user_name, d.user_pw, d.user_email, d.user_language, d.user_loginTime
     , CASE WHEN i.user_name IS NULL THEN 'D' ELSE 'U' END 
   FROM DELETED d LEFT JOIN INSERTED i ON d.user_name=i.user_name
   
   GO
   

   其中, DELETED 表格存放的是異動前的資料; INSERTED 表格是異動後的資料. 所以所謂的 UPDATE動作, 就可在 DELETED 中得到修改前的資料, INSERTED 中得到修改後的資料. 而 DELETE 動作就是在 DELETED 中有資料, 但 INSERTED 中找不到資料. 也因為 DELETE 動作不會在 INSERTED 表格中存在資料, 所以若用 INNER JOIN, 就會捕捉不到刪除的資料, 須改用 LEFT JOIN.
4. 但在實際應用上, 也許我們會想濾掉 user_loginTime 的異動紀錄. 因為使用者的登入時間會常常變動, 但其他欄位卻未更動. 如果連這個欄位的異動都做紀錄, log 表格也許會成長過快, 且產生了許多無謂的修改紀錄.  所以可將 Step3 的 trigger 修改一下: (注意: 這邊用 ALTER TRIGGER)

   ALTER TRIGGER dbo.Tr_UserAccount_Change ON dbo.UserAccount
   AFTER UPDATE,DELETE
   AS
   
   INSERT INTO dbo.UserAccount_Log (user_name, user_pw, user_email, user_language, user_loginTime, logMode)
   SELECT d.user_name, d.user_pw, d.user_email, d.user_language, d.user_loginTime
     , CASE WHEN i.user_name IS NULL THEN 'D' ELSE 'U' END 
   FROM DELETED d LEFT JOIN INSERTED i ON d.user_name=i.user_name
   WHERE i.user_name IS NULL OR d.user_loginTime=i.user_loginTime
   
   GO
   

   WHERE 條件的說明如下:
   • i.user_name IS NULL: 此條件是在資料刪除的時候成立.
   • d.user_loginTime=i.user_loginTime: 這邊簡單地做一個假設, 就是當其他欄位異動的時候, user_loginTime 這欄位的值並不會一併更改.

這樣子利用 trigger 做資料備份還是有一些缺點, 例如可能程式呼叫了 update 指令, 但卻沒有任何欄位異動. 不過可以知道的是, 使用者的確是有要更新資料, 只是他未做任何資料異動就按下了更新按鈕. 若要避免這樣的紀錄產生, 可以在 WHERE 那邊對 DELETED 和 INSERTED 做更多欄位的差異比對, 確定真的有資料異動了再寫入 log 表格.