Implement History Table

问题:

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

I was thinking about creating one history data table, that contain one identity column and an XML column with changed data as follow

ID: int
XML: Data
Name: data table name

Is this approach implemented by someone or do you see any constraints about this architecture?

回答1:

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

It uses the old INFORMATION_SCHEMA views since it relies on the column position which isn't available in any of the newer DMVs (e.g. sys.tables, sys.columns). Other than that it flies and works like a charm.

If performance becomes a problem - and this is rare - you can consider using Service Broker to implement asynchronous triggers for writing to the history table.

回答2:

Since you're using 2008, you can try to use Change Data Capture.