很多时候，都需要对数据表进行历史记录。比如每修改一次表单，之前的表单数据都需要计入历史。当表单比较多的时候，记录历史是一件比较麻烦的事情。又要建日志表，又要写存储过程，又要写页面逻辑等等。有没有通用点的办法呢？最近做项目时碰到了，要求每次审核、退回等操作时就要记录表历史。于是，笔者就想到了以下方案。在此与大家分享了，如果有更合适的或合理的建议，请回复本帖。
1）创建日志表
一个一个建表是一件烦躁的事，而且还容易出错。那么，以下存储过程就能批量建表了，还添加了LogCreateDate、LogDefaultFlag、LogPTID这3个字段。值得注意的是，创建表结构可以用以下语句“SELECT * Into tableName_Log FROM tableName”。如果只需要复制表结构，那就插入一行，再删除就是。
SQL里面实现遍历数据集不方便，不想用游标，于是采用了以下方式。具体存储过程如下：
03 | /****** Object: StoredProcedure [dbo].[CreateLogTable] Script Date: 07/02/2011 12:54:32 ******/ |
06 | SET QUOTED_IDENTIFIER ON |
10 | -- ============================================= |
12 | -- Create date: 2011-6-29 |
13 | -- Description: 创建日志表（命名规则：表名+_Log） |
14 | -- ============================================= |
15 | ALTER PROCEDURE [dbo].[CreateLogTable] |
18 | -- SET NOCOUNT ON added to prevent extra result sets from |
19 | -- interfering with SELECT statements. |
22 | -- Insert statements for procedure here |
23 | -------------------创建日志表------------------------------ |
26 | declare @tableName varchar(100) |
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%') |
40 | Select * from #tempTables |
41 | Select name into #tempCurrent from #tempTables |
42 | Delete from #tempCurrent |
44 | select @rows = @@rowcount |
47 | set @tableName=(Select top 1 name from #tempTables |
49 | (select name from #tempCurrent)) |
50 | if(@tableName is not null) |
52 | insert into #tempCurrent values(@tableName) |
53 | if object_id(@tableName+'_Log') is not null |
55 | print '表'+ @tableName +'已存在，仅做数据更新处理' |
56 | exec ('INSERT INTO'+ @tableName +'_Log SELECT * FROM '+@tableName) |
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 |
67 | ---- exec ('ALTER TABLE '+@tableName+' DROP COLUMN LogCreateDate') |
68 | ---- print '删除'+@tableName+'的列LogCreateDate成功' |
70 | ---- if(@tableName not in ('T_Shop','T_MeasurementAddress','T_TurnAround','T_IrisInstrumentHistory','T_ChainTurnApplication','T_TrainingNotice')) |
72 | ---- if col_length( @tableName+' ', 'CreateDate ') is not null |
74 | ---- exec ('ALTER TABLE '+@tableName+' DROP COLUMN CreateDate ') |
75 | ---- print '删除'+@tableName+'的列CreateDate成功' |
82 | drop table #tempCurrent |
83 | drop table #tempTables |
84 | -------------------创建日志表------------------------------ |
2）删除日志表
1 | 在开发过程中，难免会对字段进行更改。于是删除的存储过程也得有。具体代码如下： |
03 | /****** Object: StoredProcedure [dbo].[DropLogTable] Script Date: 07/02/2011 12:54:29 ******/ |
06 | SET QUOTED_IDENTIFIER ON |
08 | -- ============================================= |
10 | -- Create date: 2011-6-29 |
11 | -- Description: 删除日志表（_log） |
12 | -- ============================================= |
13 | ALTER PROCEDURE [dbo].[DropLogTable] |
16 | -- SET NOCOUNT ON added to prevent extra result sets from |
17 | -- interfering with SELECT statements. |
20 | -------------------删除日志表------------------------------ |
23 | declare @LogTableName varchar(100) |
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) |
35 | while @nIndex <= @rowsIndex |
37 | set @LogTableName=(Select top 1 name from #LogtempTables |
39 | (select name from #LogtempCurrent)) |
40 | if(@LogTableName IS NOT NULL) |
42 | insert into #LogtempCurrent values(@LogTableName) |
43 | print 'Drop table '+@LogTableName |
44 | exec ('Drop table '+@LogTableName) |
45 | print '删除'+@LogTableName+'成功' |
48 | select @nIndex = @nIndex + 1 |
50 | Drop table #LogtempTables |
51 | Drop table #LogtempCurrent |
52 | SELECT name FROM sys.sysobjects WHERE (xtype = 'U ') AND (name like '%\_log' escape '\') |
53 | -------------------删除日志表------------------------------ |
以上语句值得注意的是在查找以“_Log”结尾的表名的搜索条件，需要加上“escape '\'”。
3）记录日志
1 | 日志表有了，还得记录日志呢。为每个表写个存储过程会过于繁琐，而且改动了就得跟着改动。就是码农也吃不消。于是有了以下存储过程，该存储过程定义了7个参数，允许传入存储过程、 |
003 | /****** Object: StoredProcedure [dbo].[RecordLog] Script Date: 07/02/2011 12:54:07 ******/ |
006 | SET QUOTED_IDENTIFIER ON |
010 | -- ============================================= |
012 | -- Create date: 2011-6-29 |
014 | -- ============================================= |
015 | ALTER 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) ---主表的筛选条件 |
025 | -- SET NOCOUNT ON added to prevent extra result sets from |
026 | -- interfering with SELECT statements. |
029 | IF (@ProcedureName is not NULL) |
031 | exec ('exec('+@ProcedureName+''''+@ObjectID+''','''+@PTID+''','''+@PhasesID+''')') |
033 | ELSE IF(@InsertSQLWhere IS NOT NULL) |
035 | IF(@UpdateSQLWhere IS NOT NULL) |
037 | exec('Update '+ @tableName +'_Log Set LogDefaultFlag=0 ' +@UpdateSQLWhere) |
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 |
046 | Select @UpdateExecSQL=@UpdateExecSQL+' ObjectID='''+@ObjectID+''' ' |
050 | Select @UpdateExecSQL=@UpdateExecSQL+' ShopID='''+@ObjectID+''' ' |
054 | exec('INSERT INTO '+ @tableName +'_Log SELECT *,getdate(),1,'''+@PTID+''' FROM ' + @tableName+' '+@InsertSQLWhere) |
058 | declare @InsertSQL nvarchar(2000) |
059 | declare @UpdateSQL nvarchar(2000) |
060 | Declare @WhereCount int |
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 |
068 | select @InsertSQL=@InsertSQL+' Where DefaultFlag=1 ' |
069 | Select @WhereCount=@WhereCount+1 |
071 | --------------判断是否存在ObjectID列（门店ID）----------------------------- |
072 | if @ObjectID IS NOT NULL AND col_length( @tableName+' ', 'ObjectID ') is not null |
076 | select @InsertSQL=@InsertSQL+' AND ObjectID='''+@ObjectID+''' ' |
077 | Select @UpdateSQL=@UpdateSQL+' AND ObjectID='''+@ObjectID+''' ' |
081 | select @InsertSQL=@InsertSQL+' WHERE ObjectID='''+@ObjectID+''' ' |
082 | Select @UpdateSQL=@UpdateSQL+' WHERE ObjectID='''+@ObjectID+''' ' |
084 | Select @WhereCount=@WhereCount+1 |
086 | --------------判断是否存在ShopID列（门店ID）----------------------------- |
087 | if @ObjectID IS NOT NULL AND col_length( @tableName+' ', 'ShopID ') is not null |
091 | select @InsertSQL=@InsertSQL+' AND ShopID='''+@ObjectID+''' ' |
092 | Select @UpdateSQL=@UpdateSQL+' AND ShopID='''+@ObjectID+''' ' |
096 | select @InsertSQL=@InsertSQL+' WHERE ShopID='''+@ObjectID+''' ' |
097 | Select @UpdateSQL=@UpdateSQL+' WHERE ShopID='''+@ObjectID+''' ' |
099 | Select @WhereCount=@WhereCount+1 |
101 | --------------判断是否存在PTID列（模版ID）----------------------------- |
102 | if @PTID IS NOT NULL AND col_length( @tableName+' ', 'PTID ') is not null |
106 | select @InsertSQL=@InsertSQL+' AND PTID='''+@PTID+''' ' |
110 | select @InsertSQL=@InsertSQL+' WHERE PTID='''+@PTID+''' ' |
112 | Select @WhereCount=@WhereCount+1 |
114 | --------------判断是否存在PhasesID列（阶段ID）----------------------------- |
115 | if @PhasesID IS NOT NULL AND col_length( @tableName+' ', 'PhasesID ') is not null |
119 | select @InsertSQL=@InsertSQL+' AND PhasesID='''+@PhasesID+''' ' |
123 | select @InsertSQL=@InsertSQL+' WHERE PhasesID='''+@PhasesID+''' ' |
125 | Select @WhereCount=@WhereCount+1 |
135 | </strong>存储过程有了，再配个XML，根据参数把TableName配进去，再加点可配的自定义属性什么的，例如： |
01 | <Control Display="解约申请"> |
03 | <QueryString QueryName="PEId">D80E55971198454F97F7EBFE89D239DC</QueryString> |
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> |
09 | <Control Display="客户信息表"> |
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> |
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> |
这样，就一劳永逸了。
