很多时候，都需要对数据表进行历史记录。比如每修改一次表单，之前的表单数据都需要计入历史。当表单比较多的时候，记录历史是一件比较麻烦的事情。又要建日志表，又要写存储过程，又要写页面逻辑等等。有没有通用点的办法呢？最近做项目时碰到了，要求每次审核、退回等操作时就要记录表历史。于是，作者就想到了以下方案。在此与大家分享了，如果有更合适的或合理的建议，请回复本帖。
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
-
-
-
-
-
- ALTER PROCEDURE [dbo].[CreateLogTable]
- AS
- BEGIN
-
-
- SET NOCOUNT ON;
-
-
-
- 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)')
-
-
-
-
-
-
-
-
-
-
-
-
-
- end
- end
- select @n = @n + 1
- end
- drop table #tempCurrent
- drop table #tempTables
-
- END
|
