如何能更简洁的记录表历史

很多时候，都需要对数据表进行历史记录。比如每修改一次表单，之前的表单数据都需要计入历史。当表单比较多的时候，记录历史是一件比较麻烦的事情。又要建日志表，又要写存储过程，又要写页面逻辑等等。有没有通用点的办法呢？最近做项目时碰到了，要求每次审核、退回等操作时就要记录表历史。于是，作者就想到了以下方案。在此与大家分享了，如果有更合适的或合理的建议，请回复本帖。

1）创建日志表

一个一个建表是一件烦躁的事，而且还容易出错。那么，以下存储过程就能批量建表了，还添加了LogCreateDate、LogDefaultFlag、LogPTID这3个字段。值得注意的是，创建表结构可以用以下语句“SELECT * Into tableName_Log FROM tableName”。如果只需要复制表结构，那就插入一行，再删除就是。

SQL里面实现遍历数据集不方便，不想用游标，于是采用了以下方式。具体存储过程如下：

USE [NbShop]    
GO    
/****** Object:  StoredProcedure [dbo].[CreateLogTable]    Script Date: 07/02/2011 12:54:32 ******/    
SET ANSI_NULLS ON   
GO    
SET QUOTED_IDENTIFIER ON   
GO    
-- =============================================    
-- Author:      LWQ    
-- Create date: 2011-6-29    
-- Description: 创建日志表（命名规则：表名+_Log）    
-- =============================================    
ALTER PROCEDURE [dbo].[CreateLogTable]    
AS   
BEGIN   
    -- SET NOCOUNT ON added to prevent extra result sets from    
   -- interfering with SELECT statements.    
    SET NOCOUNT ON;    
     
    -- Insert statements for procedure here    
    -------------------创建日志表------------------------------    
declare @rows     int   
declare @n        int   
declare @tableName        varchar(100)    
select @n=1    
    SELECT     name   
    INTO            [#tempTables]    
    FROM         sys.sysobjects    
    WHERE     (xtype = 'U ') AND (name NOT IN ('sysdiagrams', 'T_BasicTime', 'T_Attribute', 'T_AttributeType', 'T_BasicTime', 'T_City','T_CompeteForMeasu',     
                          'T_DocumentTypeRestrictions', 'T_FormRelevance', 'T_HistroyShopAction', 'T_Notice', 'T_NoticeReceive', 'T_Organize', 'T_OrgType',     
                          'T_Province', 'T_Role', 'T_RptShopStatus', 'T_UploadFile', 'T_UrlPrint'))    
                          AND (name NOT LIKE '%flow%') AND (name NOT LIKE '%Control%') AND    
                          (name NOT LIKE '%Menu%') AND (name NOT LIKE '%Node%') AND (name NOT LIKE '%Log%') AND (name NOT LIKE '%Event%') AND (name NOT LIKE '%Object%') AND    
                          (name NOT LIKE '%Process%') AND (name NOT LIKE '%ShopStatus%') AND (name NOT LIKE '%Task%')     
                          AND (name NOT LIKE '%ThirdParty%') AND (name NOT LIKE '%User%')    
                          AND (name NOT LIKE '%order%')    
    Select * from   #tempTables    
    Select name into #tempCurrent  from #tempTables    
    Delete from  #tempCurrent    
     
    select @rows = @@rowcount    
    while @n <= @rows   
    begin   
      set @tableName=(Select  top 1  name from #tempTables     
      Where name not in    
      (select name from #tempCurrent))    
      if(@tableName is not null)    
      begin   
        insert into #tempCurrent values(@tableName)    
        if object_id(@tableName+'_Log') is not null    
        begin   
            print   '表'+  @tableName +'已存在，仅做数据更新处理'   
            exec ('INSERT INTO'+ @tableName +'_Log SELECT * FROM '+@tableName)                  
        end   
        else   
        begin   
            exec ('SELECT * Into '+@tableName+'_Log FROM '+@tableName)    
            print   '表'+  @tableName +'创建成功'   
            exec ('alter   table   '+@tableName+'_Log   add   LogCreateDate   datetime')    
            exec ('alter   table   '+@tableName+'_Log   add   LogDefaultFlag   int')    
            exec ('alter   table   '+@tableName+'_Log   add   LogPTID   varchar(32)')    
----            if   col_length( @tableName+' ',   'LogCreateDate ')   is not   null     
----            begin    
----                exec ('ALTER   TABLE   '+@tableName+'   DROP   COLUMN   LogCreateDate')     
----                print '删除'+@tableName+'的列LogCreateDate成功'    
----            end    
----            if(@tableName not in ('T_Shop','T_MeasurementAddress','T_TurnAround','T_IrisInstrumentHistory','T_ChainTurnApplication','T_TrainingNotice'))    
----            begin    
----                if   col_length( @tableName+' ',   'CreateDate ')   is not   null     
----                begin    
----                    exec ('ALTER   TABLE   '+@tableName+'   DROP   COLUMN   CreateDate ')    
----                    print '删除'+@tableName+'的列CreateDate成功'    
----                end     
----            end    
        end   
    end   
        select @n = @n + 1    
    end   
    drop table  #tempCurrent    
    drop table  #tempTables    
-------------------创建日志表------------------------------    
END  

#p#

2）删除日志表

在开发过程中，难免会对字段进行更改。于是删除的存储过程也得有。具体代码如下：

USE [NbShop]  
GO  
/****** Object:  StoredProcedure [dbo].[DropLogTable]    Script Date: 07/02/2011 12:54:29 ******/  
SET ANSI_NULLS ON 
GO  
SET QUOTED_IDENTIFIER ON 
GO  
-- =============================================  
-- Author:      LWQ  
-- Create date: 2011-6-29  
-- Description: 删除日志表（_log）  
-- =============================================  
ALTER PROCEDURE [dbo].[DropLogTable]  
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
 
    -------------------删除日志表------------------------------  
declare @rowsIndex     int 
declare @nIndex        int 
declare @LogTableName        varchar(100)  
select @nIndex=1  
    SELECT     name 
    INTO            #LogtempTables  
    FROM         sys.sysobjects  
    WHERE     (xtype = 'U ') AND (name like '%\_log' escape '\')    
    Select * from #LogtempTables  
    Select name into #LogtempCurrent  from #LogtempTables  
    Delete from  #LogtempCurrent  
    select @rowsIndex = (Select count(*) from   #LogtempTables)  
    print   @rowsIndex  
      
while @nIndex <= @rowsIndex  
begin 
  set @LogTableName=(Select  top 1  name from #LogtempTables   
  Where name not in   
  (select name from #LogtempCurrent))  
  if(@LogTableName IS NOT NULL)  
  begin 
    insert into #LogtempCurrent values(@LogTableName)  
    print     'Drop table '+@LogTableName  
    exec ('Drop table '+@LogTableName)  
    print    '删除'+@LogTableName+'成功' 
  end 
   
  select @nIndex = @nIndex + 1  
end 
Drop table   #LogtempTables  
Drop table   #LogtempCurrent  
SELECT     name FROM         sys.sysobjects     WHERE     (xtype = 'U ') AND (name like '%\_log' escape '\')   
-------------------删除日志表------------------------------  
END 

以上语句值得注意的是在查找以“_Log”结尾的表名的搜索条件，需要加上“escape '\'”。

3）记录日志

日志表有了，还得记录日志呢。为每个表写个存储过程会过于繁琐，而且改动了就得跟着改动。就是码农也吃不消。于是有了以下存储过程，该存储过程定义了7个参数，允许传入存储过程、表名、Where条件等。具体如下：

USE [NbShop]  
GO  
/****** Object:  StoredProcedure [dbo].[RecordLog]    Script Date: 07/02/2011 12:54:07 ******/  
SET ANSI_NULLS ON 
GO  
SET QUOTED_IDENTIFIER ON 
GO  
 
 
-- =============================================  
-- Author:      LWQ  
-- Create date: 2011-6-29  
-- Description: 记录日志  
-- =============================================  
ALTER PROCEDURE   [dbo].[RecordLog]  
    @ObjectID varchar(32),                      ---门店ID  
    @TableName varchar(100),                    ---表名  
    @PTID varchar(32),                          ---PTID（阶段ID），可选  
    @PhasesID varchar(32),                      ---阶段ID，可选  
    @ProcedureName varchar(200),                ---存储过程名称，可选  
    @InsertSQLWhere nvarchar(1000),                 ---主表的筛选条件  
    @UpdateSQLWhere nvarchar(1000)                  ---主表的筛选条件  
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
      
    IF (@ProcedureName is not NULL)  
    BEGIN 
        exec ('exec('+@ProcedureName+''''+@ObjectID+''','''+@PTID+''','''+@PhasesID+''')')  
    END 
    ELSE IF(@InsertSQLWhere IS NOT NULL)  
    BEGIN 
        IF(@UpdateSQLWhere IS NOT NULL)  
        Begin 
            exec('Update '+ @tableName +'_Log Set LogDefaultFlag=0 ' +@UpdateSQLWhere)  
        End 
        Else 
        Begin 
            declare @UpdateExecSQL nvarchar(2000)  
            Select @UpdateExecSQL='Update '+    @tableName +'_Log Set LogDefaultFlag=0 Where ' 
            --------------判断是否存在ObjectID列（门店ID）-----------------------------  
            if @ObjectID IS NOT NULL AND col_length( @tableName+' ',   'ObjectID ')   is not   null   
            BEGIN 
                Select @UpdateExecSQL=@UpdateExecSQL+' ObjectID='''+@ObjectID+''' ' 
            END       
            Else 
            Begin 
                Select @UpdateExecSQL=@UpdateExecSQL+' ShopID='''+@ObjectID+''' ' 
            END 
            exec(@UpdateExecSQL)  
        END 
        exec('INSERT INTO '+    @tableName +'_Log SELECT *,getdate(),1,'''+@PTID+''' FROM '  + @tableName+' '+@InsertSQLWhere)  
    END 
    ELSE 
    BEGIN 
        declare @InsertSQL nvarchar(2000)  
        declare @UpdateSQL nvarchar(2000)  
        Declare @WhereCount int 
        Select @WhereCount=0   
          
        Select @UpdateSQL='Update '+@tableName+'_Log Set LogDefaultFlag=0 ' 
        select @InsertSQL='INSERT INTO '+   @tableName +'_Log SELECT *,getdate(),1,'''+@PTID+''' FROM '  + @tableName;  
        --------------判断是否存在DefaultFlag列-------------------------  
        if   col_length( @tableName+' ',   'DefaultFlag ')   is not   null   
        BEGIN 
            select @InsertSQL=@InsertSQL+' Where DefaultFlag=1 ' 
            Select @WhereCount=@WhereCount+1          
        END 
        --------------判断是否存在ObjectID列（门店ID）-----------------------------  
        if @ObjectID IS NOT NULL AND col_length( @tableName+' ',   'ObjectID ')   is not   null   
        BEGIN 
            IF(@WhereCount>0)  
            BEGIN 
                select @InsertSQL=@InsertSQL+' AND ObjectID='''+@ObjectID+''' ' 
                Select @UpdateSQL=@UpdateSQL+' AND ObjectID='''+@ObjectID+''' ' 
            END 
            ELSE 
            BEGIN 
                select @InsertSQL=@InsertSQL+' WHERE ObjectID='''+@ObjectID+''' ' 
                Select @UpdateSQL=@UpdateSQL+' WHERE ObjectID='''+@ObjectID+''' ' 
            END 
            Select @WhereCount=@WhereCount+1          
        END 
        --------------判断是否存在ShopID列（门店ID）-----------------------------  
        if @ObjectID IS NOT NULL AND col_length( @tableName+' ',   'ShopID ')   is not   null   
        BEGIN 
            IF(@WhereCount>0)  
            BEGIN 
                select @InsertSQL=@InsertSQL+' AND ShopID='''+@ObjectID+''' ' 
                Select @UpdateSQL=@UpdateSQL+' AND ShopID='''+@ObjectID+''' ' 
            END 
            ELSE 
            BEGIN 
                select @InsertSQL=@InsertSQL+' WHERE ShopID='''+@ObjectID+''' ' 
                Select @UpdateSQL=@UpdateSQL+' WHERE ShopID='''+@ObjectID+''' ' 
            END 
            Select @WhereCount=@WhereCount+1          
        END 
        --------------判断是否存在PTID列（模版ID）-----------------------------  
        if @PTID IS NOT NULL AND col_length( @tableName+' ',   'PTID ')   is not   null   
        BEGIN 
            IF(@WhereCount>0)  
            BEGIN 
                select @InsertSQL=@InsertSQL+' AND PTID='''+@PTID+''' ' 
            END 
            ELSE 
            BEGIN 
                select @InsertSQL=@InsertSQL+' WHERE PTID='''+@PTID+''' ' 
            END 
            Select @WhereCount=@WhereCount+1          
        END 
        --------------判断是否存在PhasesID列（阶段ID）-----------------------------  
        if @PhasesID IS NOT NULL AND  col_length( @tableName+' ',   'PhasesID ')   is not   null   
        BEGIN 
            IF(@WhereCount>0)  
            BEGIN 
                select @InsertSQL=@InsertSQL+' AND PhasesID='''+@PhasesID+''' ' 
            END 
            ELSE 
            BEGIN 
                select @InsertSQL=@InsertSQL+' WHERE PhasesID='''+@PhasesID+''' ' 
            END 
            Select @WhereCount=@WhereCount+1          
        END 
        print @UpdateSQL  
        exec (@UpdateSQL)  
        print @InsertSQL  
        Exec  (@InsertSQL)  
    END 
     
END 

#p#

存储过程有了，再配个XML，根据参数把TableName配进去，再加点可配的自定义属性什么的，例如：

<Control Display="解约申请"> 
  <QueryStrings> 
    <QueryString QueryName="PEId">D80E55971198454F97F7EBFE89D239DC</QueryString> 
  </QueryStrings> 
  <Url><![CDATA[/FormServerTemplates/ChainsReleaseForm.aspx]]></Url> 
  <SQlTableName>T_ChainsReleaseForm</SQlTableName> 
  <SQlTableName SQlWhere=" Where CRFID=(Select top 1 CRFID from T_ChainsReleaseForm Where ShopID={@ShopID@})">T_GeneralFromTable</SQlTableName> 
</Control> 
<Control Display="客户信息表"> 
  <QueryStrings> 
    <QueryString QueryName="PEId">E515165457C5493DA605D4E66416A685</QueryString> 
    <QueryString QueryName="PEId">F9D6E25D978D4E5DB061AE33D68EE279</QueryString> 
    <QueryString QueryName="PEId">D9B9D05380EF4F11B2D2A74D0684DF4B</QueryString> 
    <QueryString QueryName="PEId">45C2B486EB7A463E94B3D55D48DB4A74</QueryString> 
    <QueryString QueryName="PEId">509B5BB3A3B14912ACD633F28A6C91A1</QueryString> 
    <QueryString QueryName="PEId">0CFE53A2A3BB4D6A891B34AA43B0FAC7</QueryString> 
    <QueryString QueryName="PEId">70247883D6414746848E0CE22F06A3F3</QueryString> 
    <QueryString QueryName="PEId">C1E2AD7DFC674DC2AA8434763D4DA0A3</QueryString> 
    <QueryString QueryName="PEId">EE895BBB5B2D43179B196F753ACADCC9</QueryString> 
  </QueryStrings> 
  <Url><![CDATA[/FormServerTemplates/AddShopInfo.aspx]]></Url> 
  <SQlTableName>T_Shop</SQlTableName> 
  <SQlTableName>T_Shopkeeper</SQlTableName> 
  <SQlTableName>T_Acreage</SQlTableName> 
  <SQlTableName>T_BusinessDistrict</SQlTableName> 
  <SQlTableName>T_Compete</SQlTableName> 
  <SQlTableName>T_SupportingFacility</SQlTableName> 
</Control> 

这样，就一劳永逸了。

原文链接：http://www.cnblogs.com/codelove/archive/2011/07/02/2096296.html

【编辑推荐】

1. SQL Server复灾 你懂了吗？
2. SQL Server管理 这些你懂吗?
3. 客户的一次疏忽，DBA的一次噩梦
4. 单数据库vs多数据库，单实例vs多实例 效率测试
5. 关于Update在不同数据库的使用