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

最新推荐文章于 2024-06-18 16:36:37 发布

gz775

最新推荐文章于 2024-06-18 16:36:37 发布

阅读量5.6k

点赞数 3

http://kaka99.iteye.com/blog/1059362

1、先为数据库建立一个字段试图，所有数据都是从系统表中提取，便于以后用户可以扩展系统功能。

    Java代码   
    
  
 CREATE VIEW dbo.V_SystemColumn  
 AS  
 SELECT DISTINCT   
       TOP 100 PERCENT dbo.sysobjects.name AS TableName, dbo.sysobjects.id,   
        dbo.sysobjects.xtype, dbo.syscolumns.name AS ColumnName,   
        dbo.syscolumns.colid, dbo.syscolumns.type, dbo.syscolumns.colstat  
 FROM dbo.sysobjects INNER JOIN  
        dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id  
 WHERE (dbo.sysobjects.xtype = 'U')  
 ORDER BY dbo.sysobjects.id, dbo.syscolumns.colid  

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

    Sql代码   
    
  
 CREATE VIEW dbo.V_Reference  
 AS  
 SELECT DISTINCT   
       TOP 100 PERCENT o1.name AS PK_TABLE_NAME, c1.name AS PK_COLUMN_NAME,   
        o2.name AS FK_TABLE_NAME, c2.name AS FK_COLUMN_NAME  
 FROM dbo.sysobjects o1 INNER JOIN  
        dbo.sysreferences r ON o1.id = r.rkeyid INNER JOIN  
        dbo.syscolumns c1 ON o1.id = c1.id AND r.rkey1 = c1.colid INNER JOIN  
        dbo.sysobjects o2 ON r.fkeyid = o2.id INNER JOIN  
        dbo.syscolumns c2 ON o2.id = c2.id AND r.fkey1 = c2.colid INNER JOIN  
        dbo.sysindexes i ON r.rkeyid = i.id AND r.rkeyindid = i.indid  
 WHERE (permissions(o1.id) <> 0) AND (permissions(o2.id) <> 0)  
 ORDER BY FK_Table_Name  

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

    Sql代码   
    
  
 CREATE Procedure GetColumnValue  
 @FKTableName Varchar(128),  
 @FKColumnName Varchar(128),  
 @FKValue Varchar(8000),  
 @ReturnValue Varchar(8000) OUTPUT  
 AS  
 declare @PkTableName Varchar(128)  
 declare @PkColumnName Varchar(128)  
 declare @PkDescriptionName Varchar(128)  
   
 declare @SqlText Varchar(8000)  
 declare @ret varchar(8000)  
   
 --获取关联主表的表名和字段名  
 select @PkTableName=Pk_Table_Name,@PkColumnName=Pk_Column_Name from V_Reference  
   Where FK_Table_Name=@FKTableName and   
          FK_Column_Name=@FKColumnName  
   
 if(@PkTableName is null)  
 begin  
   Select @ReturnValue=@FKValue  
   return 0  
 end  
 else  
 begin  
   Select Top 1 @PkDescriptionName=ColumnName   
     from V_SystemColumn   
     Where TableName=@PkTableName and ColumnName like '%Name'  
   
   
  Create Table #temp  
    (PkDescriptionName Varchar(8000) )  
   
   
   select @SqlText='   Insert Into #temp Select  '+@PkDescriptionName   
   select @SqlText=@SqlText+'    from '+@PkTableName  
   select @SqlText=@SqlText+'   Where '+@PkColumnName+'='+''''+@FKValue+''''  
   
   execute(@SqlText)  
   
   select @ReturnValue=PkDescriptionName from #temp  
 end  
 GO  

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

    Sql代码   
    
  
 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、创建日志记录存取过程

    Sql代码   
    
  
 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触发器

    Sql代码   
    
  
 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  