Keeping a history of data changes in database

Question

Every change of data in some row in database should save the previous row data in some kind of history so user can rollback to previous row data state. Is there any good practice for that approach? Tried with DataContract and serializing and deserializing data objects but it becomes little messy with complex objects.

So to be more clear:

1. I am using NHibernate for data access and want to stay out off database dependency (For testing using SQL server 2005)

2. What is my intention is to provide data history so every time user can rollback to some previous versions.

An example of usage would be the following:

• I have a news article
• Somebody make some changes to that article
• Main editor see that this news has some typos
• It decides to rollback to previous valid version (until the newest version is corrected)

I hope I gave you valid info.

Answer 1

Tables that store changes when the main table changes are called audit tables. You can do this multiple ways:

• In the database using triggers: I would recommend this approach because then there is no way that data can change without a record being made. You have to account for 3 types of changes when you do this: Add, Delete, Update. Therefore you need trigger functionality that will work on all three.

Also remember that a transaction can modify multiple records at the same time, so you should work with the full set of modified records, not just the last record (as most people belatedly realize they did).

Control will not be returned to the calling program until the trigger execution is completed. So you should keep the code as light and as fast as possible.

• In the middle layer using code: This approach will let you save changes to a different database and possibly take some load off the database. However, a SQL programmer running an UPDATE statement will completely bypass your middle layer and you will not have an audit trail.

Structure of the Audit Table

You will have the following columns:
Autonumber PK, TimeStamp, ActionType + All columns from your original table
and I have done this in the following ways in the past:

Table Structure:
Autonumber PK, TimeStamp, ActionType, TableName, OriginalTableStructureColumns

This structure will mean that you create one audit table per data table saved. The data save and reconstruction is fairly easy to do. I would recommend this approach. Name Value Pair:
Autonumber PK, TimeStamp, ActionType, TableName, PKColumns, ColumnName, OldValue, NewValue

This structure will let you save any table, but you will have to create name value pairs for each column in your trigger. This is very generic, but expensive. You will also need to write some views to recreate the actual rows by unpivoting the data. This gets to be tedious and is not generally the method followed.

Answer 2

Microsoft have introduced new auditing capabilities into SQL Server 2008. Here's an article describing some of the capabilities and design goals which might help in whichever approach you choose.

MSDN - Auditing in SQL Server 2008