Often, it's necessary to maintain historical records of database tables. For example, when forms are modified, previous versions should be preserved. With many forms involved, managing history becomes complex—requiring log tables, stored procedures, and page logic implementations. Is there a more universal approach? Recently, while working on a project that required logging changes during approval or rejection processes, I developed the following solution.

1) Creating Log Tables

Manually creating individual tables is tedious and error-prone. The following stored procedure automates this process, including three additional fields: LogCreateDate, LogDefaultFlag, and LogPTID. To create table structures, you can use "SELECT * INTO tableName_Log FROM tableName". If you only need the schema, insert one row then delete it.

Since SQL lacks convenient iteration over datasets, I avoided cursors and used an alternative method. Here's the stored procedure:

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:	Create log tables (naming convention: table_name+_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
	-------------------Create log tables------------------------------
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   'Table '+  @tableName +' already exists, performing data update'
			exec ('INSERT INTO'+ @tableName +'_Log SELECT * FROM '+@tableName)				
		end
		else
		begin
			exec ('SELECT * Into '+@tableName+'_Log FROM '+@tableName)
			print   'Table '+  @tableName +' created successfully'
			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 'Deleted column LogCreateDate from '+@tableName+' successfully'
----			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 'Deleted column CreateDate from '+@tableName+' successfully'
----				end	
----			end
		end
	  end
		select @n = @n + 1
	end
	drop table	#tempCurrent
	drop table	#tempTables
-------------------Create log tables------------------------------
END

<br></br>

<strong>2) Removing Log Tables</strong>

During development, fields might change requiring deletion of tables. Here's the corresponding stored procedure:

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:	Drop log tables (_log)
-- =============================================
ALTER PROCEDURE [dbo].[DropLogTable]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -------------------Drop log tables------------------------------
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	 'Successfully deleted '+@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 '\') 
-------------------Drop log tables------------------------------
END

Note the escape sequence in the search conditino for tables ending with "_log".

<strong>3) Logging Changes</strong>

With log tables in place, we need to implement logging. Writing individual stored procedures per table would be cumbersome. Thus, I created a flexible stored procedure with seven parameters allowing dynamic inputs for table names and conditions. Here's its definition:

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:	Log changes
-- =============================================
ALTER PROCEDURE   [dbo].[RecordLog]
	@ObjectID varchar(32),						---Store ID
	@TableName varchar(100),					---Table name
	@PTID varchar(32),							---PTID (Phase ID), optional
	@PhasesID varchar(32),						---Phase ID, optional
	@ProcedureName varchar(200),				---Stored procedure name, optional
	@InsertSQLWhere nvarchar(1000),					---Main table filter criteria
	@UpdateSQLWhere nvarchar(1000)					---Main table filter criteria
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 '
			--------------Check for ObjectID column-----------------------------
			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;
		--------------Check for DefaultFlag column-------------------------
		if   col_length( @tableName+' ',   'DefaultFlag ')   is not   null 
		BEGIN
			select @InsertSQL=@InsertSQL+' Where DefaultFlag=1 '
			Select @WhereCount=@WhereCount+1		
		END
		--------------Check for ObjectID column (Store 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
		--------------Check for ShopID column (Store 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
		--------------Check for PTID column (Template 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
		--------------Check for PhasesID column (Phase 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
<strong>
</strong>With the stored procedure ready, integrate it with XML configuration. Map TableName dynamically through parameters and include configurable attributes. Example:

  <Control Display="Termination Request">
    <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="Customer Information">
    <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>

This setup provides a long-term solution for tracking changes across multiple tables.