SQL-记录表历史-白红宇

SQL-记录表历史

阅读量：5311 次

发布时间：2019-06-14

本文共 10651 字，大约阅读时间需要 35 分钟。

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

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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:		LWQ-- Create date: 2011-6-29-- Description:	创建日志表（命名规则：表名+_Log）-- =============================================ALTER PROCEDURE [dbo].[CreateLogTable]ASBEGIN	-- SET NOCOUNT ON added to prevent extra result sets from	-- interfering with SELECT statements.	SET NOCOUNT ON;    -- Insert statements for procedure here	-------------------创建日志表------------------------------declare @rows     intdeclare @n        intdeclare @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

2）删除日志表

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

USE [NbShop]GO/****** Object:  StoredProcedure [dbo].[DropLogTable]    Script Date: 07/02/2011 12:54:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:		LWQ-- Create date: 2011-6-29-- Description:	删除日志表（_log）-- =============================================ALTER PROCEDURE [dbo].[DropLogTable]ASBEGIN	-- SET NOCOUNT ON added to prevent extra result sets from	-- interfering with SELECT statements.	SET NOCOUNT ON;    -------------------删除日志表------------------------------declare @rowsIndex     intdeclare @nIndex        intdeclare @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 <= @rowsIndexbegin  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 + 1endDrop table	 #LogtempTablesDrop table	 #LogtempCurrentSELECT     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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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)					---主表的筛选条件ASBEGIN	-- 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存储过程有了，再配个XML，根据参数把TableName配进去，再加点可配的自定义属性什么的，例如：


        
           
      
       D80E55971198454F97F7EBFE89D239DC
          
         
     
         
     
      T_ChainsReleaseForm
         
     
      T_GeneralFromTable
       
      
        
           
      
       E515165457C5493DA605D4E66416A685
            
      
       F9D6E25D978D4E5DB061AE33D68EE279
            
      
       D9B9D05380EF4F11B2D2A74D0684DF4B
            
      
       45C2B486EB7A463E94B3D55D48DB4A74
            
      
       509B5BB3A3B14912ACD633F28A6C91A1
            
      
       0CFE53A2A3BB4D6A891B34AA43B0FAC7
            
      
       70247883D6414746848E0CE22F06A3F3
            
      
       C1E2AD7DFC674DC2AA8434763D4DA0A3
            
      
       EE895BBB5B2D43179B196F753ACADCC9
          
         
     
         
     
      T_Shop
         
     
      T_Shopkeeper
         
     
      T_Acreage
         
     
      T_BusinessDistrict
         
     
      T_Compete
         
     
      T_SupportingFacility

这样，就一劳永逸了。

转载于:https://www.cnblogs.com/codelove/archive/2011/07/02/2096296.html

你可能感兴趣的文章

苹果手表：大方向和谷歌一样，硬件分道扬镳