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

数据库 SQL Server 数据库运维
很多时候,都需要对数据表进行历史记录。比如每修改一次表单,之前的表单数据都需要计入历史。当表单比较多的时候,记录历史是一件比较麻烦的事情。有没有通用点的办法呢?于是,作者就想到了以下方案。在此与大家分享了,如果有更合适的或合理的建议,请回复本帖。

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

1)创建日志表

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

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

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

#p#

2)删除日志表

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

  1. USE [NbShop]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[DropLogTable]    Script Date: 07/02/2011 12:54:29 ******/  
  4. SET ANSI_NULLS ON 
  5. GO  
  6. SET QUOTED_IDENTIFIER ON 
  7. GO  
  8. -- =============================================  
  9. -- Author:      LWQ  
  10. -- Create date: 2011-6-29  
  11. -- Description: 删除日志表(_log)  
  12. -- =============================================  
  13. ALTER PROCEDURE [dbo].[DropLogTable]  
  14. AS 
  15. BEGIN 
  16.     -- SET NOCOUNT ON added to prevent extra result sets from  
  17.     -- interfering with SELECT statements.  
  18.     SET NOCOUNT ON;  
  19.  
  20.     -------------------删除日志表------------------------------  
  21. declare @rowsIndex     int 
  22. declare @nIndex        int 
  23. declare @LogTableName        varchar(100)  
  24. select @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.       
  35. while @nIndex <= @rowsIndex  
  36. begin 
  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  
  49. end 
  50. Drop table   #LogtempTables  
  51. Drop table   #LogtempCurrent  
  52. SELECT     name FROM         sys.sysobjects     WHERE     (xtype = 'U 'AND (name like '%\_log' escape '\')   
  53. -------------------删除日志表------------------------------  
  54. END 

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

3)记录日志

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

  1. USE [NbShop]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[RecordLog]    Script Date: 07/02/2011 12:54:07 ******/  
  4. SET ANSI_NULLS ON 
  5. GO  
  6. SET QUOTED_IDENTIFIER ON 
  7. GO  
  8.  
  9.  
  10. -- =============================================  
  11. -- Author:      LWQ  
  12. -- Create date: 2011-6-29  
  13. -- Description: 记录日志  
  14. -- =============================================  
  15. ALTER PROCEDURE   [dbo].[RecordLog]  
  16.     @ObjectID varchar(32),                      ---门店ID  
  17.     @TableName varchar(100),                    ---表名  
  18.     @PTID varchar(32),                          ---PTID(阶段ID),可选  
  19.     @PhasesID varchar(32),                      ---阶段ID,可选  
  20.     @ProcedureName varchar(200),                ---存储过程名称,可选  
  21.     @InsertSQLWhere nvarchar(1000),                 ---主表的筛选条件  
  22.     @UpdateSQLWhere nvarchar(1000)                  ---主表的筛选条件  
  23. AS 
  24. BEGIN 
  25.     -- SET NOCOUNT ON added to prevent extra result sets from  
  26.     -- interfering with SELECT statements.  
  27.     SET NOCOUNT ON;  
  28.       
  29.     IF (@ProcedureName is not NULL)  
  30.     BEGIN 
  31.         exec ('exec('+@ProcedureName+''''+@ObjectID+''','''+@PTID+''','''+@PhasesID+''')')  
  32.     END 
  33.     ELSE IF(@InsertSQLWhere IS NOT NULL)  
  34.     BEGIN 
  35.         IF(@UpdateSQLWhere IS NOT NULL)  
  36.         Begin 
  37.             exec('Update '+ @tableName +'_Log Set LogDefaultFlag=0 ' +@UpdateSQLWhere)  
  38.         End 
  39.         Else 
  40.         Begin 
  41.             declare @UpdateExecSQL nvarchar(2000)  
  42.             Select @UpdateExecSQL='Update '+    @tableName +'_Log Set LogDefaultFlag=0 Where ' 
  43.             --------------判断是否存在ObjectID列(门店ID)-----------------------------  
  44.             if @ObjectID IS NOT NULL AND col_length( @tableName+' ',   'ObjectID ')   is not   null   
  45.             BEGIN 
  46.                 Select @UpdateExecSQL=@UpdateExecSQL+' ObjectID='''+@ObjectID+''' ' 
  47.             END       
  48.             Else 
  49.             Begin 
  50.                 Select @UpdateExecSQL=@UpdateExecSQL+' ShopID='''+@ObjectID+''' ' 
  51.             END 
  52.             exec(@UpdateExecSQL)  
  53.         END 
  54.         exec('INSERT INTO '+    @tableName +'_Log SELECT *,getdate(),1,'''+@PTID+''' FROM '  + @tableName+' '+@InsertSQLWhere)  
  55.     END 
  56.     ELSE 
  57.     BEGIN 
  58.         declare @InsertSQL nvarchar(2000)  
  59.         declare @UpdateSQL nvarchar(2000)  
  60.         Declare @WhereCount int 
  61.         Select @WhereCount=0   
  62.           
  63.         Select @UpdateSQL='Update '+@tableName+'_Log Set LogDefaultFlag=0 ' 
  64.         select @InsertSQL='INSERT INTO '+   @tableName +'_Log SELECT *,getdate(),1,'''+@PTID+''' FROM '  + @tableName;  
  65.         --------------判断是否存在DefaultFlag列-------------------------  
  66.         if   col_length( @tableName+' ',   'DefaultFlag ')   is not   null   
  67.         BEGIN 
  68.             select @InsertSQL=@InsertSQL+' Where DefaultFlag=1 ' 
  69.             Select @WhereCount=@WhereCount+1          
  70.         END 
  71.         --------------判断是否存在ObjectID列(门店ID)-----------------------------  
  72.         if @ObjectID IS NOT NULL AND col_length( @tableName+' ',   'ObjectID ')   is not   null   
  73.         BEGIN 
  74.             IF(@WhereCount>0)  
  75.             BEGIN 
  76.                 select @InsertSQL=@InsertSQL+' AND ObjectID='''+@ObjectID+''' ' 
  77.                 Select @UpdateSQL=@UpdateSQL+' AND ObjectID='''+@ObjectID+''' ' 
  78.             END 
  79.             ELSE 
  80.             BEGIN 
  81.                 select @InsertSQL=@InsertSQL+' WHERE ObjectID='''+@ObjectID+''' ' 
  82.                 Select @UpdateSQL=@UpdateSQL+' WHERE ObjectID='''+@ObjectID+''' ' 
  83.             END 
  84.             Select @WhereCount=@WhereCount+1          
  85.         END 
  86.         --------------判断是否存在ShopID列(门店ID)-----------------------------  
  87.         if @ObjectID IS NOT NULL AND col_length( @tableName+' ',   'ShopID ')   is not   null   
  88.         BEGIN 
  89.             IF(@WhereCount>0)  
  90.             BEGIN 
  91.                 select @InsertSQL=@InsertSQL+' AND ShopID='''+@ObjectID+''' ' 
  92.                 Select @UpdateSQL=@UpdateSQL+' AND ShopID='''+@ObjectID+''' ' 
  93.             END 
  94.             ELSE 
  95.             BEGIN 
  96.                 select @InsertSQL=@InsertSQL+' WHERE ShopID='''+@ObjectID+''' ' 
  97.                 Select @UpdateSQL=@UpdateSQL+' WHERE ShopID='''+@ObjectID+''' ' 
  98.             END 
  99.             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.      
  133. END 

#p#

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

  1. <Control Display="解约申请"> 
  2.   <QueryStrings> 
  3.     <QueryString QueryName="PEId">D80E55971198454F97F7EBFE89D239DC</QueryString> 
  4.   </QueryStrings> 
  5.   <Url><![CDATA[/FormServerTemplates/ChainsReleaseForm.aspx]]></Url> 
  6.   <SQlTableName>T_ChainsReleaseForm</SQlTableName> 
  7.   <SQlTableName SQlWhere=" Where CRFID=(Select top 1 CRFID from T_ChainsReleaseForm Where ShopID={@ShopID@})">T_GeneralFromTable</SQlTableName> 
  8. </Control> 
  9. <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> 

这样,就一劳永逸了。

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

【编辑推荐】

  1. SQL Server复灾 你懂了吗?
  2. SQL Server管理 这些你懂吗?
  3. 客户的一次疏忽,DBA的一次噩梦
  4. 单数据库vs多数据库,单实例vs多实例 效率测试
  5. 关于Update在不同数据库的使用
责任编辑:艾婧 来源: 雪雁的博客
相关推荐

2012-03-01 09:37:17

iOSVOIP

2023-06-26 08:17:53

拉链表数据结构

2011-05-12 10:48:49

CSS清理浮动

2017-03-20 18:53:42

征信记录金融服务

2020-08-06 16:34:48

Python开发工具

2017-04-21 12:03:21

互联网

2023-06-19 15:36:30

JavaScrip技巧开发

2022-08-28 19:03:18

JavaScript编程语言开发

2019-06-21 13:52:19

PythonPython 3.8编程语言

2011-04-29 10:22:49

CSS高性能Web开发

2020-10-04 13:15:37

代码技术开发

2022-05-10 10:28:21

JavaScript代码

2021-12-22 10:18:00

建木九州云DevOps

2021-01-31 08:00:48

Windows 10Windows操作系统

2009-02-05 10:14:00

2011-10-09 14:57:35

2023-11-23 13:50:00

Python代码

2019-07-31 10:24:16

JavaScript浏览器口袋妖怪

2023-10-29 12:54:16

Doris数据仓库

2011-04-06 16:25:47

SQL Server
点赞
收藏

51CTO技术栈公众号