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
2）删除日志表
在开发过程中，难免会对字段进行更改。于是删除的存储过程也得有。具体代码如下：
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）记录日志
日志表有了，还得记录日志呢。为每个表写个存储过程会过于繁琐，而且改动了就得跟着改动。就是码农也吃不消。于是有了以下存储过程，该存储过程定义了7个参数，允许传入存储过程、
表名、Where条件等。具体如下：
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
存储过程有了，再配个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>
这样，就一劳永逸了。
作者：雪雁
出处：http://www.cnblogs.com/codelove/
沟通渠道：编程交流群<85318032> 产品交流群<897857351>
如果喜欢作者的文章，请关注【麦扣聊技术】订阅号以便第一时间获得最新内容。本文版权归作者和湖南心莱信息科技有限公司共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
静听鸟语花香，漫赏云卷云舒。
出处：http://www.cnblogs.com/codelove/
沟通渠道：编程交流群<85318032> 产品交流群<897857351>
如果喜欢作者的文章，请关注【麦扣聊技术】订阅号以便第一时间获得最新内容。本文版权归作者和湖南心莱信息科技有限公司共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
静听鸟语花香，漫赏云卷云舒。
