利用觸發器自動記錄資料的變化

利用觸發器自動記錄資料的變化
一、        需求的產生
    在以資料為主的公司專屬應用程式中，有時需要記錄敏感性資料的變化。比如有關職工的個人資訊，需要保證資訊的準確性和安全性，不能夠隨意更改，每次的更改都要詳細的記錄下修改時間，修改前後欄位的內容，操作員等。下面讓我們看看如何在SQL SERVER資料庫上實現這個功能。我的應用程式環境是WinForm的C/S結構，沒有測試其他環境。
二、        如何有效儲存資料變化產生的資訊
如果另外建立一個和原資料表結構一樣的資料表用來儲存資料的變化，這顯然不是一個好主意，資料冗餘量大，不利於以後的查看，並且不能記錄額外的資訊。我們可以設計一個通用的資料表結構來儲存資料的變化資訊：

CREATE TABLE [dbo].[DataRecorder](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [TableName] [nvarchar](50) NOT NULL,

    [KeyValue] [varchar](50) NOT NULL,

    [FieldName] [nvarchar](50) NOT NULL,

    [OldValue] [nvarchar](500) NULL,

    [NewValue] [nvarchar](500) NULL,

    [RecordDate] [smalldatetime] NOT NULL CONSTRAINT [DF_LIIInsuInfo_RecordDate] DEFAULT (getdate()),

 [Recorder] [nvarchar](20) NOT NULL)

欄位的說明：

[TableName]指存在資料變化的資料表名稱，既然是通用的表，就要區分是記錄的哪個表。

[KeyValue] 指存在資料變化的資料表中的關鍵字段值，用於知道記錄的是哪條記錄。

[FieldName]指哪個欄位存在資料變化。

[OldValue]記錄資料變化前的值，重要，如果想改回以前的值，這個必須要記錄。

[NewValue]記錄資料變化後的新值。

[Recorder]記錄是誰操作的。嘿嘿，想偷偷提高自己的工資標準？小樣，後台記著呢。
三、        如何不受人為影響自動記錄，降低程式設計的複雜性，提高通用性

    既然自動記錄，首先想到的當然是利用觸發器了。

    首先遇到的第一個痛點是，如何記錄操作員。參考前人做法，建一個記錄使用者登入的資料表(UserLogin)，只需要三個欄位，記錄使用者名稱(UserName)、登入機器的名稱(HostName)和登入時間(LoginTime).這兒假定資料操作是由同一台PC最近登入的使用者進行的。
    第二個問題是欄位名，我們有時需要直觀的欄位名稱，如果直接給出縮寫後的欄位名，可能會引起混淆，這兒可以利用列屬性中的說明來解決，在建立資料表時在每個列屬性中的說明中指定直觀有意義的名稱，只要取出這個說明就可以儲存有意義的欄位名稱。
    第三個問題是有些欄位不需要進行記錄，比如一些內容較多的說明性的大欄位。需要在程式中排除。
    好了，下面讓我們看看觸發器的主要部分：

 

CREATE TRIGGER [dbo].[tr_Update]
   ON 
   AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT mid=IDENTITY(int,1,1),* INTO #i FROM inserted      
    IF @@rowcount=0 RETURN  
    SELECT mid=IDENTITY(int,1,1),* INTO #d FROM deleted  
   
    DECLARE @TableName nvarchar(10),@Recorder nvarchar(20),@s nvarchar(4000)  
   
    SELECT TOP 1 @TableName='職工資訊表', @Recorder=UserName FROM UserLogin
            WHERE HostName=host_name() ORDER BY LoginTime DESC
   
    DECLARE #tb CURSOR LOCAL FOR SELECT
        'INSERT DataRecorder (TableName,KeyValue,FieldName,OldValue,NewValue,Recorder) SELECT @TableName,CAST(i.EmployeeID AS nvarchar),'''
        +CAST(ISNULL(b.[value], a.name) AS char(50))+''',CAST(d.['+a.name+'] AS nvarchar),CAST(i.['+a.name+'] AS nvarchar),@Recorder FROM #d d,#i i WHERE i.mid=d.mid AND i.[' +a.name+'] <> d.['+a.name+']'
    FROM   syscolumns a LEFT JOIN sys.extended_properties b  ON a.id = b.major_id AND a.colid = b.minor_id     
    WHERE  a.id=object_id('Employee') AND (Substring(columns_updated(),(a.colid-1)/8+1,1)&power(2,(a.colid-1)%8))=power(2,(a.colid-1)%8) AND a.name NOT IN ('Remarks','Address')
    ORDER BY a.colid 
   
    OPEN #tb  
    FETCH #tb INTO @s  
    WHILE @@fetch_status=0  
    BEGIN  
        EXEC sp_executesql @s,N'@TableName nvarchar(10),@Recorder nvarchar(20)',@TableName,@Recorder
        FETCH #tb INTO @s  
    END  
    CLOSE #tb  
    DEALLOCATE #tb
END
     以上程式是比較早前完成的，當時參考了前輩高人（具體是哪位大俠，已經記不清了）的作品，在此對前輩高人表示感謝。