如何利用Trigger來建立資料表異動記錄(SQL SERVER)

    在歷經產品上線後，我們常常被詢問到一些問題：

     

    • 哪些資料是經常被更新的?
    • 哪些資料表是不常被修改的?
    • 是誰將該資料改為100，是什麼時候改的?
    • 這商品的價錢是什麼時候改為99元的?

     

    而為了達到此需求有幾種的解決方案：

     

    • 在程式設計的時候就加入log的機制，當有異動的內容全都記錄下來
    • 可利用資料庫的功能，例如利用trigger的功能來記錄資料表的異動

     

    本篇說明如果利用trigger的方式：

    在網路參考到一篇類似的作法 Nigel Rivett’s SQL Server Auditing triggers，但其原理為建立一audit表格，其用來記錄目前標的的表格異動，當異動產生 ，原封不動記錄異動每個欄位資料至audit表格中(但多加一timestamp欄位)。

    但以上做法在網路上討論也有效能的爭議。而是否有折衷的做法，在每次異動時，僅記錄有異動的欄位即可，請看下面的介紹：

     

    首先建立一Audit表格，用來記錄異動資料，script由此下載，其欄位如下：

     

    auditTable：

    欄位說明

    Type：異動行為: U(更新)、i(新增)、D(刪除)

    TableName：資料表名

    PrimaryKeyField：主鍵名稱(P K)

    PrimaryKeyValue：主鍵值(PK value)

    FieldName：有異動的欄位名稱

    OldValue：原資料值（最多取varchar(1000)，超過會截斷）

    NewValue：異動後的值（最多取varchar(1000)，超過會截斷）

    UpdateDate：異動時間

    UpdateName：異動使用者名稱(若是程式就是connect string中填的帳號)

     

    產生Trigger 的script 從這下載，

    注意:必須將以下變數換成實際的值

    __YOUR_TRIGGER_NAME ：產生的trigger的名稱

    __YOUR_TABLE_NAME（有二個地方要換）：要偵測異動目標的表格名稱