How to keep an audit/history of changes to the table

Question

I've been asked to create a simple DataGrid-style application to edit a single table of a database, and that's easy enough. But part of the request is to create an audit trail of changes made, who made them, and the date/time.

How might you solve this kind of thing?

(I'll be using C# in VS2008, ADO.NET connected to SQL Server 2005, WPF and Xceed's DataGrid, if it makes any difference.)

Answer 1

I'd log them in another table. Just structure the table to include the information you want.

Answer 2

The best way to do this is set up triggers in the database that write to audit tables.

Answer 3

There are two common ways of creating audit trails.

1. Code your data access layer.
2. In the database itself using triggers.

There are advantages and disadvantages to both. Some people prefer one over the other. It's often down to the type of app and the type of database use you can expect.

If you do it in your DA layer it's pretty much up to you. You just need to add code to every method that saves to the database to also save a log of the changes. This auditing code could be in your DA layer code, or even in your stored procs in your database if you are using stored procs for everything. Essentially the premise is the same, any time you make a change to the database, log that change.

If you want to go down the triggers route, you can write custom triggers for each table, or fashion a more generic trigger that works the same on lots of tables. Check out this article on audit triggers. This works by firing of triggers whenever a change is made, and the triggers log the changes. Remember that if you want to audit SELECT statements, you can't use triggers, you'll have to do that with in code/stored proc auditing. It's also worth remember that depending on your database, triggers may not fire in all circumstances. For example, most databases don't fire triggers during TRUNCATE statements. Check that your triggers get fired in any case that you need auditing.

Alternately, you could also take a look at using the service broker to do async auditing on a dedicated machine. This is more complex and takes a bit of configuring to set up.

Which ever way you do it you need to decide on the format the audit log will take. Normally you would save this log in your database, but you could just save it in a log file or whatever suits your requirements. You could use a single audit table that logs all changes, or you could have an audit table per main table being audited. For large scale implementations you could even consider putting the audit tables in a totally separate database. If your logging into a table, it's common to have a "change type" field which indicates if the audited change was an insert, update or delete style of change, along with the changed data, user who made the change and the date/time the change was made. Don't forget to include the old and new data for update style changes.

Answer 4

Most universal method would be to create another table for storing versions of record from the first table. Then, you can remove all the data from main table. Suppose you need versioning of a table Person(PersonId, Name, Surname):

CREATE TABLE Person 
(
   PersonId INT,                   // PK
   CurrentPersonVersion INT        // FK
);

CREATE TABLE PersonVersion
(
  PersonVersionId INT,             // PK
  PersonID                         // FK 
  Name VARCHAR,                    // actual data
  Surname VARCHAR,                 // actual data

  ChangeDate                       // logging data
  ChangeAuthor                     // logging data
)

Now any change requires inserting new PersonVersion and updating the CurrentPersonVersionID.

Answer 5

The other way of doing this apart from triggers is this, 1) Have five columns, UpdFlag, DelFlag, EffectiveDate and TerminatedDate for each table you want to do an audit trail on. 2) code your sproc's in such a way that when you do an update, to pass in the all of the row's column data into the sproc, update the row by setting the TerminatedDate to the date that was updated, and mark the UpdFlag and to put in the datetime into the column 3) Then create a new row with the new data (which is really updated). and put in a new date now for the EffectiveDate and the TerminatedDate set to the max date.

Likewise if you want to do a deletion of the row, simply update the row by marking the DelFlag as set, the TerminatedDate with the datetime now. You are in effect doing a soft delete and not an actual sql's Delete.

In that way, when you want to audit the data, and to show a trail of the changes, you can simply filter the rows for those that have the UpdFlag set, or between EffectiveDate and TerminatedDate. Likewise for those that were deleted, you filter for those that have the DelFlag set or between EffectiveDate and TerminatedDate. For the current rows, filter the rows that have both flags set off. The advantage is you don't have to create another table for the audit when the trigger is used!

Hope this makes sense, Best regards, Tom.

Answer 6

I'd go triggers route, by creating table with similar structure to updated one, with additional columns for tracking changes like ModifiedAt etc. And then adding on update trigger that will insert changes to that table. I find it easier to maintain than have everything in the application code. Ofcourse many people tend to forget about triggers when it comes to questions like 'wtf this table is changing' ;) Cheers.

Answer 7

Ditto use triggers.

Anyone considering soft deletion should have a read of Richard Dingwall's The trouble with soft delete.