很多時候，都需要對數據表進行歷史記錄。比如每修改一次表單，之前的表單數據都需要計入歷史。當表單比較多的時候，記錄歷史是一件比較麻煩的事情。又要建日志表，又要寫存儲過程，又要寫頁面邏輯等等。有沒有通用點的辦法呢？最近做項目時碰到了，要求每次審核、退回等操作時就要記錄表歷史。于是，作者就想到了以下方案。在此與大家分享了，如果有更合適的或合理的建議，請回復本帖。

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在不同數據庫的使用