利用触发器自动记录数据的变化

qiang · 发表于 2009-12-15 09:01

一、需求的产生

在以数据为主的企业应用中，有时需要记录敏感数据的变化。比如有关职工的个人信息，需要保证信息的准确性和安全性，不能够随意更改，每次的更改都要详细的记录下修改时间，修改前后字段的内容，操作员等。下面让我们看看如何在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