SQL-记录表历史[转] 转自：http://www.cnblogs.com/codelove/archive/2011/07/02/2096296.html （自用，备份）

SQL-记录表历史[转] 转自：http://www.cnblogs.com/codelove/archive/2011/07/02/2096296.html （自用，备份）

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

1）创建日志表

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

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

01USE [NbShop]
02GO
03/****** Object:  StoredProcedure [dbo].[CreateLogTable]    Script Date: 07/02/2011 12:54:32 ******/
04SET ANSI_NULLS ON
05GO
06SET QUOTED_IDENTIFIER ON
07GO
08 
09 
10-- =============================================
11-- Author:      LWQ
12-- Create date: 2011-6-29
13-- Description: 创建日志表（命名规则：表名+_Log）
14-- =============================================
15ALTER PROCEDURE [dbo].[CreateLogTable]
16AS
17BEGIN
18    -- SET NOCOUNT ON added to prevent extra result sets from
19    -- interfering with SELECT statements.
20    SET NOCOUNT ON;
21 
22    -- Insert statements for procedure here
23    -------------------创建日志表------------------------------
24declare @rows     int
25declare @n        int
26declare @tableName        varchar(100)
27select @n=1
28    SELECT     name
29    INTO            [#tempTables]
30    FROM         sys.sysobjects
31    WHERE     (xtype = 'U ') AND (name NOT IN ('sysdiagrams', 'T_BasicTime', 'T_Attribute', 'T_AttributeType', 'T_BasicTime', 'T_City','T_CompeteForMeasu',
32                          'T_DocumentTypeRestrictions', 'T_FormRelevance', 'T_HistroyShopAction', 'T_Notice', 'T_NoticeReceive', 'T_Organize', 'T_OrgType',
33                          'T_Province', 'T_Role', 'T_RptShopStatus', 'T_UploadFile', 'T_UrlPrint'))
34                          AND (name NOT LIKE '%flow%') AND (name NOT LIKE '%Control%') AND
35                          (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
36                          (name NOT LIKE '%Process%') AND (name NOT LIKE '%ShopStatus%') AND (name NOTLIKE '%Task%')
37                          AND (name NOT LIKE '%ThirdParty%') AND (name NOT LIKE '%User%')
38                          AND (name NOT LIKE '%order%')
39     
40    Select * from   #tempTables
41    Select name into #tempCurrent  from #tempTables
42    Delete from  #tempCurrent
43 
44     select @rows = @@rowcount
45    while @n <= @rows
46    begin
47      set @tableName=(Select  top 1  name from #tempTables
48      Where name not in
49      (select name from #tempCurrent))
50      if(@tableName is not null)
51      begin
52        insert into #tempCurrent values(@tableName)
53        if object_id(@tableName+'_Log') is not null
54        begin
55            print   '表'+  @tableName +'已存在，仅做数据更新处理'
56            exec ('INSERT INTO'+ @tableName +'_Log SELECT * FROM '+@tableName)             
57        end
58        else
59        begin
60            exec ('SELECT * Into '+@tableName+'_Log FROM '+@tableName)
61            print   '表'+  @tableName +'创建成功'
62            exec ('alter   table   '+@tableName+'_Log   add   LogCreateDate   datetime')
63            exec ('alter   table   '+@tableName+'_Log   add   LogDefaultFlag   int')
64            exec ('alter   table   '+@tableName+'_Log   add   LogPTID   varchar(32)')
65----            if   col_length( @tableName+' ',   'LogCreateDate ')   is not   null
66----            begin
67----                exec ('ALTER   TABLE   '+@tableName+'   DROP   COLUMN   LogCreateDate')
68----                print '删除'+@tableName+'的列LogCreateDate成功'
69----            end
70----            if(@tableName not in ('T_Shop','T_MeasurementAddress','T_TurnAround','T_IrisInstrumentHistory','T_ChainTurnApplication','T_TrainingNotice'))
71----            begin
72----                if   col_length( @tableName+' ',   'CreateDate ')   is not   null
73----                begin
74----                    exec ('ALTER   TABLE   '+@tableName+'   DROP   COLUMN   CreateDate ')
75----                    print '删除'+@tableName+'的列CreateDate成功'
76----                end
77----            end
78        end
79      end
80        select @n = @n + 1
81    end
82    drop table  #tempCurrent
83    drop table  #tempTables
84-------------------创建日志表------------------------------
85END
1<br>
2）删除日志表
1在开发过程中，难免会对字段进行更改。于是删除的存储过程也得有。具体代码如下：
01USE [NbShop]
02GO
03/****** Object:  StoredProcedure [dbo].[DropLogTable]    Script Date: 07/02/2011 12:54:29 ******/
04SET ANSI_NULLS ON
05GO
06SET QUOTED_IDENTIFIER ON
07GO
08-- =============================================
09-- Author:      LWQ
10-- Create date: 2011-6-29
11-- Description: 删除日志表（_log）
12-- =============================================
13ALTER PROCEDURE [dbo].[DropLogTable]
14AS
15BEGIN
16    -- SET NOCOUNT ON added to prevent extra result sets from
17    -- interfering with SELECT statements.
18    SET NOCOUNT ON;
19 
20    -------------------删除日志表------------------------------
21declare @rowsIndex     int
22declare @nIndex        int
23declare @LogTableName        varchar(100)
24select @nIndex=1
25    SELECT     name
26    INTO            #LogtempTables
27    FROM         sys.sysobjects
28    WHERE     (xtype = 'U ') AND (name like '%\_log' escape '\') 
29    Select * from #LogtempTables
30    Select name into #LogtempCurrent  from #LogtempTables
31    Delete from  #LogtempCurrent
32    select @rowsIndex = (Select count(*) from   #LogtempTables)
33    print   @rowsIndex
34     
35while @nIndex <= @rowsIndex
36begin
37  set @LogTableName=(Select  top 1  name from #LogtempTables
38  Where name not in
39  (select name from #LogtempCurrent))
40  if(@LogTableName IS NOT NULL)
41  begin
42    insert into #LogtempCurrent values(@LogTableName)
43    print     'Drop table '+@LogTableName
44    exec ('Drop table '+@LogTableName)
45    print    '删除'+@LogTableName+'成功'
46  end
47  
48  select @nIndex = @nIndex + 1
49end
50Drop table   #LogtempTables
51Drop table   #LogtempCurrent
52SELECT     name FROM         sys.sysobjects     WHERE     (xtype = 'U ') AND (name like '%\_log' escape '\')
53-------------------删除日志表------------------------------
54END

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

3）记录日志
1日志表有了，还得记录日志呢。为每个表写个存储过程会过于繁琐，而且改动了就得跟着改动。就是码农也吃不消。于是有了以下存储过程，该存储过程定义了7个参数，允许传入存储过程、
1表名、Where条件等。具体如下：
001USE [NbShop]
002GO
003/****** Object:  StoredProcedure [dbo].[RecordLog]    Script Date: 07/02/2011 12:54:07 ******/
004SET ANSI_NULLS ON
005GO
006SET QUOTED_IDENTIFIER ON
007GO
008 
009 
010-- =============================================
011-- Author:      LWQ
012-- Create date: 2011-6-29
013-- Description: 记录日志
014-- =============================================
015ALTER PROCEDURE   [dbo].[RecordLog]
016    @ObjectID varchar(32),                      ---门店ID
017    @TableName varchar(100),                    ---表名
018    @PTID varchar(32),                          ---PTID（阶段ID），可选
019    @PhasesID varchar(32),                      ---阶段ID，可选
020    @ProcedureName varchar(200),                ---存储过程名称，可选
021    @InsertSQLWhere nvarchar(1000),                 ---主表的筛选条件
022    @UpdateSQLWhere nvarchar(1000)                  ---主表的筛选条件
023AS
024BEGIN
025    -- SET NOCOUNT ON added to prevent extra result sets from
026    -- interfering with SELECT statements.
027    SET NOCOUNT ON;
028     
029    IF (@ProcedureName is not NULL)
030    BEGIN
031        exec ('exec('+@ProcedureName+''''+@ObjectID+''','''+@PTID+''','''+@PhasesID+''')')
032    END
033    ELSE IF(@InsertSQLWhere IS NOT NULL)
034    BEGIN
035        IF(@UpdateSQLWhere IS NOT NULL)
036        Begin
037            exec('Update '+ @tableName +'_Log Set LogDefaultFlag=0 ' +@UpdateSQLWhere)
038        End
039        Else
040        Begin
041            declare @UpdateExecSQL nvarchar(2000)
042            Select @UpdateExecSQL='Update '+    @tableName +'_Log Set LogDefaultFlag=0 Where '
043            --------------判断是否存在ObjectID列（门店ID）-----------------------------
044            if @ObjectID IS NOT NULL AND col_length( @tableName+' ',   'ObjectID ')   is not   null
045            BEGIN
046                Select @UpdateExecSQL=@UpdateExecSQL+' ObjectID='''+@ObjectID+''' '
047            END    
048            Else
049            Begin
050                Select @UpdateExecSQL=@UpdateExecSQL+' ShopID='''+@ObjectID+''' '
051            END
052            exec(@UpdateExecSQL)
053        END
054        exec('INSERT INTO '+    @tableName +'_Log SELECT *,getdate(),1,'''+@PTID+''' FROM '  + @tableName+' '+@InsertSQLWhere)
055    END
056    ELSE
057    BEGIN
058        declare @InsertSQL nvarchar(2000)
059        declare @UpdateSQL nvarchar(2000)
060        Declare @WhereCount int
061        Select @WhereCount=0
062         
063        Select @UpdateSQL='Update '+@tableName+'_Log Set LogDefaultFlag=0 '
064        select @InsertSQL='INSERT INTO '+   @tableName +'_Log SELECT *,getdate(),1,'''+@PTID+''' FROM '  + @tableName;
065        --------------判断是否存在DefaultFlag列-------------------------
066        if   col_length( @tableName+' ',   'DefaultFlag ')   is not   null
067        BEGIN
068            select @InsertSQL=@InsertSQL+' Where DefaultFlag=1 '
069            Select @WhereCount=@WhereCount+1       
070        END
071        --------------判断是否存在ObjectID列（门店ID）-----------------------------
072        if @ObjectID IS NOT NULL AND col_length( @tableName+' ',   'ObjectID ')   is not   null
073        BEGIN
074            IF(@WhereCount>0)
075            BEGIN
076                select @InsertSQL=@InsertSQL+' AND ObjectID='''+@ObjectID+''' '
077                Select @UpdateSQL=@UpdateSQL+' AND ObjectID='''+@ObjectID+''' '
078            END
079            ELSE
080            BEGIN
081                select @InsertSQL=@InsertSQL+' WHERE ObjectID='''+@ObjectID+''' '
082                Select @UpdateSQL=@UpdateSQL+' WHERE ObjectID='''+@ObjectID+''' '
083            END
084            Select @WhereCount=@WhereCount+1       
085        END
086        --------------判断是否存在ShopID列（门店ID）-----------------------------
087        if @ObjectID IS NOT NULL AND col_length( @tableName+' ',   'ShopID ')   is not   null
088        BEGIN
089            IF(@WhereCount>0)
090            BEGIN
091                select @InsertSQL=@InsertSQL+' AND ShopID='''+@ObjectID+''' '
092                Select @UpdateSQL=@UpdateSQL+' AND ShopID='''+@ObjectID+''' '
093            END
094            ELSE
095            BEGIN
096                select @InsertSQL=@InsertSQL+' WHERE ShopID='''+@ObjectID+''' '
097                Select @UpdateSQL=@UpdateSQL+' WHERE ShopID='''+@ObjectID+''' '
098            END
099            Select @WhereCount=@WhereCount+1       
100        END
101        --------------判断是否存在PTID列（模版ID）-----------------------------
102        if @PTID IS NOT NULL AND col_length( @tableName+' ',   'PTID ')   is not   null
103        BEGIN
104            IF(@WhereCount>0)
105            BEGIN
106                select @InsertSQL=@InsertSQL+' AND PTID='''+@PTID+''' '
107            END
108            ELSE
109            BEGIN
110                select @InsertSQL=@InsertSQL+' WHERE PTID='''+@PTID+''' '
111            END
112            Select @WhereCount=@WhereCount+1       
113        END
114        --------------判断是否存在PhasesID列（阶段ID）-----------------------------
115        if @PhasesID IS NOT NULL AND  col_length( @tableName+' ',   'PhasesID ')   is not   null
116        BEGIN
117            IF(@WhereCount>0)
118            BEGIN
119                select @InsertSQL=@InsertSQL+' AND PhasesID='''+@PhasesID+''' '
120            END
121            ELSE
122            BEGIN
123                select @InsertSQL=@InsertSQL+' WHERE PhasesID='''+@PhasesID+''' '
124            END
125            Select @WhereCount=@WhereCount+1       
126        END
127        print @UpdateSQL
128        exec (@UpdateSQL)
129        print @InsertSQL
130        Exec  (@InsertSQL)
131    END
132    
133END
134<strong>
135</strong>存储过程有了，再配个XML，根据参数把TableName配进去，再加点可配的自定义属性什么的，例如：
1 
01<Control Display="解约申请">
02  <QueryStrings>
03    <QueryString QueryName="PEId">D80E55971198454F97F7EBFE89D239DC</QueryString>
04  </QueryStrings>
05  <Url><![CDATA[/FormServerTemplates/ChainsReleaseForm.aspx]]></Url>
06  <SQlTableName>T_ChainsReleaseForm</SQlTableName>
07  <SQlTableName SQlWhere=" Where CRFID=(Select top 1 CRFID from T_ChainsReleaseForm Where ShopID={@ShopID@})">T_GeneralFromTable</SQlTableName>
08</Control>
09<Control Display="客户信息表">
10  <QueryStrings>
11    <QueryString QueryName="PEId">E515165457C5493DA605D4E66416A685</QueryString>
12    <QueryString QueryName="PEId">F9D6E25D978D4E5DB061AE33D68EE279</QueryString>
13    <QueryString QueryName="PEId">D9B9D05380EF4F11B2D2A74D0684DF4B</QueryString>
14    <QueryString QueryName="PEId">45C2B486EB7A463E94B3D55D48DB4A74</QueryString>
15    <QueryString QueryName="PEId">509B5BB3A3B14912ACD633F28A6C91A1</QueryString>
16    <QueryString QueryName="PEId">0CFE53A2A3BB4D6A891B34AA43B0FAC7</QueryString>
17    <QueryString QueryName="PEId">70247883D6414746848E0CE22F06A3F3</QueryString>
18    <QueryString QueryName="PEId">C1E2AD7DFC674DC2AA8434763D4DA0A3</QueryString>
19    <QueryString QueryName="PEId">EE895BBB5B2D43179B196F753ACADCC9</QueryString>
20  </QueryStrings>
21  <Url><![CDATA[/FormServerTemplates/AddShopInfo.aspx]]></Url>
22  <SQlTableName>T_Shop</SQlTableName>
23  <SQlTableName>T_Shopkeeper</SQlTableName>
24  <SQlTableName>T_Acreage</SQlTableName>
25  <SQlTableName>T_BusinessDistrict</SQlTableName>
26  <SQlTableName>T_Compete</SQlTableName>
27  <SQlTableName>T_SupportingFacility</SQlTableName>
28</Control>

这样，就一劳永逸了。

静听鸟语花香，漫赏云卷云舒。一花一世界，一树一菩提，一码一人生。
posted @ 2011-07-04 11:40  Benny Ng  阅读(236)  评论(0)    收藏  举报