Sometimes you want to keep track of everything that has been added, deleted, or changed in a Microsoft SQL Server table. There are different methods to do that. Here’s one, I haven’t seen this exact method described elsewhere.

The idea is, start with an existing table. Add two columns named BeginDate and EndDate to the table. Each row is active from the BeginDate to the EndDate inclusive.

Make a view whose columns match the original table. In other words, the BeginDate and EndDate columns are not visible in the view.

Make triggers on the view to handle inserts, updates, and deletes. The insert trigger will add a row to the underlying table with a BeginDate of now. The delete trigger will set the EndDate of the rows to now. and the update trigger will do a delete followed by an insert.

Now the underlying table shows all entries that have ever been made in the table, and the view show all entries that are currently active in the table.

For example, to see what the active entries were on 2013-01-01 12:30, you’d execute this query against the underlying table:

[sql]
SELECT * FROM <underlyingtable> WHERE ‘2013-01-01 12:30’ BETWEEN BeginDate and EndDate;
[/sql]

There are more compact ways of doing this in some circumstances. The advantage of doing it this way is that you can apply this method to almost any table without planning ahead of time, and it works for many different database products from different vendors. A disadvantage, of course, is that you’re storing everything with no de-duplication, unless that’s done by the underlying database engine.

I’ve done this so often that I’ve developed a stored procedure for Microsoft SQL Server to convert a normal table to a history table. My convention is to append “History” to the name of the table, and to name the new view the same as the original table. Here’s that stored procedure; it accepts one parameter, the original table name. As always, please be cautious about running this stored procedure on your system; please back up your data etc.

[sql]
CREATE PROCEDURE [dbo].[AddHistoryToTable](@tableName VARCHAR(128)) AS
begin
declare @newTableName varchar(8000);
set @newTableName = @tableName + ‘History’;

declare @viewDef varchar(8000);
declare @alterDef1 varchar(8000);
declare @alterDef2 varchar(8000);
declare @alterDef3 varchar(8000);
declare @alterDef4 varchar(8000);
declare @insertDef varchar(8000);
declare @insertDef1 varchar(8000);
declare @insertDef2 varchar(8000);
declare @deleteDef varchar(8000);
declare @updateDef varchar(8000);
declare @updateDef1 varchar(8000);
declare @updateDef2 varchar(8000);
declare @updateDef3 varchar(8000);

set @alterDef1 = ‘ALTER TABLE ‘ + @newTableName + ‘ ADD BeginDate DATETIME NULL, EndDate DATETIME NULL’;
set @alterDef2 = ‘UPDATE ‘ + @newTableName + ‘ SET BeginDate=”1900-01-01”, EndDate=”2999-12-31”’;
set @alterDef3 = ‘ALTER TABLE ‘ + @newTableName + ‘ ALTER COLUMN BeginDate DATETIME NOT NULL’;
set @alterDef4 = ‘ALTER TABLE ‘ + @newTableName + ‘ ALTER COLUMN EndDate DATETIME NOT NULL’;
set @viewDef = ‘CREATE VIEW ‘ + @tableName + ‘ AS SELECT ‘;
set @insertDef1 = ‘CREATE TRIGGER ‘ + @tableName + ‘Insert ON ‘ + @tableName + ‘ INSTEAD OF INSERT AS INSERT INTO ‘ + @newTableName + ‘ (‘;
set @insertDef2 = ”;
set @deleteDef = ‘CREATE TRIGGER ‘ + @tableName + ‘Delete ON ‘ + @tableName + ‘ INSTEAD OF DELETE AS UPDATE ‘ + @newTableName + ‘ SET EndDate=GETDATE() FROM deleted WHERE ‘;
set @updateDef1 = ‘CREATE TRIGGER ‘ + @TableName + ‘Update ON ‘ + @TableName + ‘ INSTEAD OF UPDATE AS UPDATE ‘ + @newTableName + ‘ SET EndDate=GETDATE() FROM deleted WHERE ‘;
set @updateDef2 = ‘;INSERT INTO ‘ + @newTableName + ‘ (‘;
set @updateDef3 = ”;

declare @colname varchar(100);
declare @delim varchar(1);
declare @delim2 varchar(5);

declare colnames cursor local forward_only read_only for select COLUMN_NAME from information_schema.columns where table_name = @TableName order by ORDINAL_POSITION;

open colnames;

fetch next from colnames into @colname;

set @delim = ”;
set @delim2 = ”;

while @@Fetch_Status = 0
begin
set @viewDef = @ViewDef + @delim + @colname;
set @insertDef1 = @insertDef1 + @delim + @colname;
set @insertDef2 = @insertDef2 + @delim + @colname;
set @deleteDef = @deleteDef + @delim2 + @newTableName + ‘.’ + @colname + ‘=deleted.’ + @colname;
set @updateDef1 = @updateDef1 + @delim2 + @newTableName + ‘.’ + @colname + ‘=deleted.’ + @colname;
set @updateDef2 = @updateDef2 + @delim + @colname;
set @updateDef3 = @updateDef3 + @delim + @colname;
fetch next from colnames into @colname;
set @delim = ‘,’;
set @delim2 = ‘ and ‘;
end;

close colnames;

deallocate colnames;

set @viewDef = @viewDef + ‘ FROM ‘ + @newTableName + ‘ WHERE GETDATE() BETWEEN BeginDate AND EndDate’;
set @insertDef1 = @insertDef1 + @delim + ‘BeginDate,EndDate) SELECT ‘;
set @insertDef2 = @insertDef2 + @delim + ‘GETDATE(),”2999-12-31” from inserted’;
set @deleteDef = @deleteDef + @delim2 + @newTableName + ‘.EndDate >= GETDATE()’;
set @updateDef1 = @updateDef1 + @delim2 + @newTableName + ‘.EndDate >= GETDATE()’;
set @updateDef2 = @updateDef2 + @delim + ‘BeginDate,EndDate) SELECT ‘;
set @updateDef3 = @updateDef3 + @delim + ‘GETDATE(),”2999-12-31” from inserted’;

set @insertDef = @insertDef1 + @insertDef2;
set @updateDef = @updateDef1 + @updateDef2 + @updateDef3;

print ‘execute sp_rename ”’ + @tableName + ”’, ”’ + @newTableName + ”’, ”OBJECT”’;
print ‘GO’;
print @alterDef1;
print ‘GO’;
print @alterDef2;
print ‘GO’;
print @alterDef3;
print ‘GO’;
print @alterDef4;
print ‘GO’;
print @viewDef;
print ‘GO’;
print @deleteDef;
print ‘GO’;
print @insertDef;
print ‘GO’;
print @updateDef;
print ‘GO’;
end;
GO
[/sql]

Hope this helps someone!

Image credit: LiquidSky64

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available