Implement a history table

Question

I want to implement the history functionality in my application, but I want to skip creating a history data table for each business object data table.

I was thinking of creating one history data table containing one identifier column and an XML column with the changed data, as follows

ID: int XML: Data Name: data table name 

Is this approach implemented by someone or do you see any restrictions regarding this architecture?

Answer 1

I have used a modified version of this article - Adding a simple trigger audit to your SQL Server database - for many years.

It uses the old INFORMATION_SCHEMA views because it relies on a column position that is not available in any of the new DMVs (e.g. sys.tables , sys.columns ). In addition, he flies and works like a charm.

If performance becomes a problem - and this is rare, you can use Service Broker to implement asynchronous triggers to write to the history table.

Answer 2

Since you are using 2008, you can try to use Change Data Collection .