如何通过触发器实现记录数据的修改日志

最近我在一个项目中需要记录任何人对数据库中字段修改前后值做记录。由于是采用B/S结构，如果采用全部由asp代码实现，整个工作量可想而知了（这个项目中涉及100多个表）。我想通过触发器实现此功能，充分利用Sql Server 数据库中提供的系统表处理日志。
处理步骤如下：
1、先为数据库建立一个字段试图，所有数据都是从系统表中提取，便于以后用户可以扩展系统功能。

 1CREATE VIEW dbo.V_SystemColumn
 2AS
 3SELECT DISTINCT 
 4      TOP 100 PERCENT dbo.sysobjects.name AS TableName, dbo.sysobjects.id, 
 5      dbo.sysobjects.xtype, dbo.syscolumns.name AS ColumnName, 
 6      dbo.syscolumns.colid, dbo.syscolumns.type, dbo.syscolumns.colstat
 7FROM dbo.sysobjects INNER JOIN
 8      dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id
 9WHERE (dbo.sysobjects.xtype = 'U')
10ORDER BY dbo.sysobjects.id, dbo.syscolumns.colid
11
12

2、建立一个各个表之间关联的视图。

 1CREATE VIEW dbo.V_Reference
 2AS
 3SELECT DISTINCT 
 4      TOP 100 PERCENT o1.name AS PK_TABLE_NAME, c1.name AS PK_COLUMN_NAME, 
 5      o2.name AS FK_TABLE_NAME, c2.name AS FK_COLUMN_NAME
 6FROM dbo.sysobjects o1 INNER JOIN
 7      dbo.sysreferences r ON o1.id = r.rkeyid INNER JOIN
 8      dbo.syscolumns c1 ON o1.id = c1.id AND r.rkey1 = c1.colid INNER JOIN
 9      dbo.sysobjects o2 ON r.fkeyid = o2.id INNER JOIN
10      dbo.syscolumns c2 ON o2.id = c2.id AND r.fkey1 = c2.colid INNER JOIN
11      dbo.sysindexes i ON r.rkeyid = i.id AND r.rkeyindid = i.indid
12WHERE (permissions(o1.id) <> 0) AND (permissions(o2.id) <> 0)
13ORDER BY FK_Table_Name
14
15

3、创建一个存取过程，参数为：表名、列名、Insert.列名的值、返回参数

 1CREATE Procedure GetColumnValue
 2@FKTableName Varchar(128),
 3@FKColumnName Varchar(128),
 4@FKValue Varchar(8000),
 5@ReturnValue Varchar(8000) OUTPUT
 6AS
 7declare @PkTableName Varchar(128)
 8declare @PkColumnName Varchar(128)
 9declare @PkDescriptionName Varchar(128)
10
11declare @SqlText Varchar(8000)
12declare @ret varchar(8000)
13
14--获取关联主表的表名和字段名
15select @PkTableName=Pk_Table_Name,@PkColumnName=Pk_Column_Name from V_Reference
16  Where FK_Table_Name=@FKTableName and 
17        FK_Column_Name=@FKColumnName
18
19if(@PkTableName is null)
20begin
21  Select @ReturnValue=@FKValue
22  return 0
23end
24else
25begin
26  Select Top 1 @PkDescriptionName=ColumnName 
27    from V_SystemColumn 
28    Where TableName=@PkTableName and ColumnName like '%Name'
29
30
31 Create Table #temp
32  (PkDescriptionName Varchar(8000) )
33
34
35  select @SqlText='  Insert Into #temp Select  '+@PkDescriptionName 
36  select @SqlText=@SqlText+'   from '+@PkTableName
37  select @SqlText=@SqlText+'  Where '+@PkColumnName+'='+''''+@FKValue+''''
38
39  execute(@SqlText)
40
41  select @ReturnValue=PkDescriptionName from #temp
42end
43GO
44

4、为系统创建记录日志的表

CREATE TABLE T_SystemLog (
       TableName            varchar(128) NULL,
       KeyValue             varchar(20) NOT NULL,
       FieldName            varchar(128) NULL,
       OldValue             varchar(8000) NULL,
       NewValue             varchar(8000) NULL,
       Modifier             varchar(20) NULL,
       ModifyDate           datetime NULL DEFAULT CURRENT_TIMESTAMP
)
go

5、创建日志记录存取过程

CREATE Procedure Logger
@TableName Varchar(128),
@ColumnName Varchar(128),
@KeyValue int,
@OldValue Varchar(8000),
@NewValue Varchar(8000),
@LastModifier Varchar(20)
AS
if(@OldValue<>@NewValue)
begin
  exec GetColumnValue @TableName,@ColumnName,@OldValue,@OldValue Output 
  exec GetColumnValue @TableName,@ColumnName,@NewValue,@NewValue Output
  Insert Into T_SystemLog(TableName,KeyValue,FieldName,OldValue,NewValue,Modifier,ModifyDate)
    Values( @TableName,@KeyValue,@ColumnName,@OldValue,@NewValue,@LastModifier,getdate())
end
GO

6、为需要记录修改日志的表创建Insert、Update触发器

CREATE trigger uti_corp on T_Corp
for Update
AS
set nocount on
declare @KeyValue int
declare @OldValue Varchar(8000)
declare @NewValue varchar(8000)
declare @LastModifier varchar(8000)

if(update(departmentid))
begin
  select @KeyValue=corpid,@NewValue=departmentid,@LastModifier=LastModifier from inserted
  select @OldValue=departmentid from deleted
  execute Logger 'T_Corp','DepartmentID',@KeyValue,@OldValue,@NewValue,@LastModifier
end

此方法可以实现记录用户修改数据中的任意字段的历史数据和修改后的数据，但中间存在一个问题，没有出来批量修改的情况，需要以后完善。