随笔- 210 文章- 0 评论- 1680 阅读- 119万

SQL-记录表历史

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

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

<br>

2）删除日志表

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

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）记录日志

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

1	`表名、Where条件等。具体如下：`

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

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
<strong>
</strong>存储过程有了，再配个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>