History Tables for SQL Server
09-Feb-2021
History tables are used in several MBARI msSQLServer databases – including Expd, VNTA_Divelog, DOCR_Divelog and possibly others.
They provide a mechanism to track changes to the data over time in the tables they represent. The history table is created with all of the columns of the original table, plus a ‘modified’ timestamp column and a ‘resultof’ column. Along with the history table itself, there are triggers placed on the watched table for any insert, update, or delete data operation.
History table functionality can be implemented on any base table by first creating two stored procedures in the target database (and granting execute permission). Then the first procedure is run to create the history table – once for each table you want to track. Once the history tables exists, a second procedure can be run for each base table, to place the insert, update, delete triggers on each base table.
Exec AdminGenerateHistoryTable mybasetablename
Exec AdminGenerateTriggers mybasetablename
AdminGenerateHistoryTable uses a sort of ‘introspection’ to create a table with column names and types that match the target base table (plus the timestamp and resultof columns). By convention, the history table takes on the name of the base table with a prefix of ‘History_’
A third stored procedure exists (AdminDropHistoryTriggers) that can be used to drop the triggers in the base table. This can be useful to temporarily remove the triggers in the case you need to do mass changes on the base table that you do not need tracked.
AdminGenerateHistoryTable
--- Procedure: AdminGenerateHistoryTable
--- Author: R.Schramm
--- Purpose: Create the history table used to maintain the history of modification to table data.
--- Usage: Takes one parameter,,, the table name that is the target.
--- Misc: The table triggers must be created after running this stored procedure.
--- (See AdminGenerateHistoryTriggers)
--- Misc. Columns are generated dynamically to match the base table, except for
--- Misc. any column named rowguid is omitted - assumed to be for replication - which
--- would cause a 'race' condition on merge replication
--- MaintenanceHistory: 12-Oct-2004. added skip of rowguid columns.
/* Compatibility updates for SQL Server 2016: */
/* 1. AdminGenerateHistoryTable no longer generated from initializeHistoryTablesSTEP1.sql script. */
/* 2. Undocumented system table queries replaced with system views. */
/* 3. AdminGetTypeString sp replaced with sys.dm_exec_describe_first_result_set dynamic function. */
/* Neil Conner 8/7/2018 */
CREATE PROCEDURE [dbo].[AdminGenerateHistoryTable] @TableName sysname
as
declare @cmd nvarchar(4000)
declare @bigcmd nvarchar(4000)
declare @dest_owner nvarchar(255)
declare @dest_tabname sysname
declare @src_objid int
declare @artcolumns binary(32)
declare @art_col int
declare @this_col int
declare @spacer nvarchar(1)
declare @nullability nvarchar(10)
declare @typestring nvarchar(255)
declare @col_name sysname
-------- create temp table for command fragments
create table #proctext ( c1 int identity NOT NULL,
procedure_text nvarchar(4000) collate database_default )
select @dest_owner = 'dbo'
select @dest_tabname = 'History_'+@TableName
Select @src_objid = object_id FROM sys.objects WHERE type = 'U'
and name = @TableName
-- script out CREATE TABLE statement
-- begin create table
select @cmd = N'create table [dbo].' + QUOTENAME(@dest_tabname) + N'('
insert into #proctext(procedure_text) values( @cmd )
select @cmd = ' [modified] [datetime] NULL'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = ',[resultof] [varchar] (50) NULL'
insert into #proctext(procedure_text) values( @cmd )
-- columns
select @art_col = 1
select @spacer = N' '
DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR
select column_id from sys.columns where object_id = @src_objid order by column_id asc
OPEN hCColid
FETCH hCColid INTO @this_col
WHILE (@@fetch_status <> -1)
begin
------ delete this spacer if your are not adding columns above... -rs
select @spacer = N','
select @col_name = name,
@nullability = case is_nullable
when 0 then N'NOT NULL'
else N'NULL' end
from sys.columns where object_id=@src_objid and @this_col=column_id
if (@col_name < > 'rowguid')
begin --- skip replication rowguids
SET @typestring = (
SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM ' + @TableName,NULL,0)
WHERE NAME = @col_name
)
select @cmd = @spacer + QUOTENAME(@col_name) + N' ' + @typestring + N' ' + @nullability
insert into #proctext(procedure_text) values( @cmd )
select @art_col = @art_col + 1
select @spacer = N','
End --- skip rowguids
FETCH hCColid INTO @this_col
end
CLOSE hCColid
DEALLOCATE hCColid
-- end create table
insert into #proctext(procedure_text) values( N')' )
----- select procedure_text from #proctext order by c1 asc
--- extract temp query and execute
select @bigcmd = ''
DECLARE mycursor CURSOR LOCAL FAST_FORWARD FOR
select c1 from #proctext order by c1 asc
OPEN mycursor
FETCH mycursor INTO @this_col
WHILE (@@fetch_status <> -1)
begin
select @cmd = procedure_text from #proctext where c1 = @this_col
select @bigcmd = @bigcmd + @cmd
FETCH mycursor INTO @this_col
end
CLOSE mycursor
DEALLOCATE mycursor
exec ( @bigcmd)
GO
AdminGenerateTriggers
--- Procedure: AdminGenerateHistoryTriggers
--- Author: R.Schramm
--- Purpose: Create the insert, update and delete triggers to maintain the history of modification to table
--- data.
--- Usage: Takes one parameter,,, the table name that is the target.
--- Misc: The history table must exist prior to running this stored procedure (See AdminGenerateHistoryTable)
--- Misc: Any pre-existing history triggers are dropped prior to creating new triggers,
--- Misc. Any column named rowguid is omitted - assumed to be for replication.
--- Misc: Also checks that the column list are consistent between the history table and the base table.
--- MaintenanceHistory: pre 12-Oct-2004. did an insert select * from the modified row. This didnt sit well
--- with replication which adds a rowguid on the end of each table... we dont want to persist the
--- rowguid if we turn on replication.
--- 25-May-2005 update now takes record form inserted, so history table has complete record.
/* Compatibility updates for SQL Server 2016: */
/* 1. Undocumented system table queries replaced with system views. */
/* Neil Conner 8/7/2018 */
CREATE PROCEDURE [dbo].[AdminGenerateHistoryTriggers]
@TableName sysname
AS
set nocount on
---- test if the base table exists.
if NOT exists ( SELECT * FROM sys.objects WHERE type = 'U' and name = @TableName )
begin
print 'ERROR: invalid table name: ' + @TableName
select 'ERROR: invalid table name: ' + @TableName
return 208 -- object does not exist
end
---- get its list of columns
DECLARE @columnlist varchar(4000)
DECLARE @status INT
EXEC @status = AdminGetColumnList @TableName, @columnlist OUTPUT
--- dont want the (comma)rowguid if its there., replauce with emptystring
select @columnlist = replace( @columnlist , ',rowguid','')
---- test if the history table exists
if NOT exists ( SELECT * FROM sys.objects WHERE type = 'U' and name = 'History_'+@TableName )
begin
print 'ERROR: the history table doesnt exist yet for ' + @TableName
select 'ERROR: the history table doesnt exist yet for ' + @TableName
return 208 -- object does not exist
end
---- get the history table list of columns
DECLARE @historycolumnlist varchar(4000)
DECLARE @historytablename varchar(4000)
select @historytablename = 'History_'+@TableName
EXEC @status = AdminGetColumnList @historytablename, @historycolumnlist OUTPUT
----- compare the column lists for the base and history tables.
DECLARE @tmpstr varchar(4000)
Select @tmpstr = substring(@historycolumnlist,19,4000)
--- select @columnlist
---select @historytablename
---select @historycolumnlist
---select @tmpstr
if ( @tmpstr <> @columnlist )
begin
print 'ERROR: the history table columns do not match with the base table column list. ' + @TableName
select'ERROR: the history table columns do not match with the base table column list. ' + @TableName
return 208 -- object does not exist
end
--- construct command to drop any old history triggers.
DECLARE @Body_G varchar(1000), @Body_I varchar(4000), @Body_U varchar(4000), @Body_D varchar(4000)
DECLARE @Table sysname, @Schema sysname
SELECT @Table =PARSENAME(@TableName,1), @Schema=ISNULL(PARSENAME(@TableName, 2), CURRENT_USER)
------- @Body_G holds command to drop triggers if they exist
SELECT @Body_G='
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('''+@Schema+'.iTR_History_'
+@Table
+'''))
Drop Trigger '+@Schema+'.iTR_History_'+@Table+'
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('''+@Schema+'.uTR_History_'
+@Table
+'''))
Drop Trigger '+@Schema+'.uTR_History_'+@Table+'
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('''+@Schema+'.dTR_History_'
+@Table
+'''))
Drop Trigger '+@Schema+'.dTR_History_'+@Table+'
'
----- now build the insert, update and delete triggers
----- init other strings to empty
Select @Body_I =''
Select @Body_U =''
Select @Body_D =''
----- prepare insert update and delete statement roots
select @Body_I ='
---- Warning: DO NOT MODIFY BY HAND ! This trigger is automatically generated.
Create Trigger iTR_History_'+@Table +' ON '+ @TableName +'
For Insert
NOT FOR REPLICATION
As
set nocount on
'
select @Body_U='
---- Warning: DO NOT MODIFY BY HAND ! This trigger is automatically generated.
Create Trigger uTR_History_'+@Table +' ON '+@TableName+ '
For Update
NOT FOR REPLICATION
As
set nocount on
'
select @Body_d='
---- Warning: DO NOT MODIFY BY HAND ! This trigger is automatically generated.
Create Trigger dTR_History_'+@Table+' ON '+@TableName+'
For Delete
NOT FOR REPLICATION
As
set nocount on
'
select @Body_I = @Body_I + '
IF EXISTS (Select * from Inserted)
Insert into .dbo.History_'+@Table+' (modified, resultof,'+@columnlist + ')
Select GETDATE(),''Insert'',' + @columnlist + ' from Inserted '
SELECT @Body_U =@Body_U + '
IF EXISTS (Select * from Inserted)
Insert into .dbo.History_'+@Table+' (modified, resultof,'+@columnlist + ')
Select GETDATE(),''Update'',' + @columnlist + ' from Inserted '
Select @Body_D = @Body_D+'
IF EXISTS (Select * from Deleted)
Insert into .dbo.History_'+@Table+' (modified, resultof,'+@columnlist + ')
Select GETDATE(),''Delete'',' + @columnlist + ' from Deleted '
Execute (@Body_G) --- drop existing if they exist
Execute (@Body_I)
Execute (@Body_U)
Execute (@Body_D)
GO
AdminDropHistoryTriggers
--- Procedure: AdminDropHistoryTriggers
--- Author: R.Schramm
--- Purpose: Remove the insert, update and delete triggers that maintain the history of modification to table
--- data.
--- Usage: Takes one parameter,,, the table name that is the target.
--- MaintenanceHistory: pre 12-Oct-2004. did an insert select * from the modified row. This didnt sit well
--- with replication which adds a rowguid on the end of each table... we dont want to persist the
--- rowguid if we turn on replication.
/* Compatibility updates for SQL Server 2016: */
/* 1. Undocumented system table queries replaced with system views. */
/* Neil Conner 8/7/2018 */
CREATE PROCEDURE [dbo].[AdminDropHistoryTriggers]
@TableName sysname
AS
set nocount on
---- test if the history table exists
if NOT exists ( SELECT * FROM sys.objects WHERE type = 'U' and name = 'History_'+@TableName )
begin
print 'ERROR: the history table doesnt exist yet for ' + @TableName
select 'ERROR: the history table doesnt exist yet for ' + @TableName
return 208 -- object does not exist
end
--- construct command to drop any old history triggers.
DECLARE @Body_G varchar(1000)
DECLARE @Table sysname, @Schema sysname
SELECT @Table =PARSENAME(@TableName,1), @Schema=ISNULL(PARSENAME(@TableName, 2), CURRENT_USER)
------- @Body_G holds command to drop triggers if they exist
SELECT @Body_G='
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('''+@Schema+'.iTR_History_'
+@Table
+'''))
Drop Trigger '+@Schema+'.iTR_History_'+@Table+'
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('''+@Schema+'.uTR_History_'
+@Table
+'''))
Drop Trigger '+@Schema+'.uTR_History_'+@Table+'
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('''+@Schema+'.dTR_History_'
+@Table
+'''))
Drop Trigger '+@Schema+'.dTR_History_'+@Table+'
'
Execute (@Body_G) --- drop existing if they exist
GO