Patterns for implementing field change tracking.

Question

Hi all

For one of my recent projects, I had to implement field change tracking. So anytime the user changed a value of a field, the change was recorded in order to allow full auditing of changes.

In the database, I implemented this as a single table 'FieldChanges' with the following fields: TableName, FieldName, RecordId, DateOfChange, ChangedBy, IntValue, TextValue, DateTimeValue, BoolValue.

The sproc saving changes to an object determines for each field whether it has been changed and inserts a record into FieldChanges if it has: if the type of the changed field is int, it records it in the IntValue field in the FieldChanges table, etc.

This means that for any field in any table with any id value, I can query the FieldChanges table to get a list of changes.

This works quite well but is a bit clumsy. Can anyone else who has implemented similar functionality suggest a better approach, and why they think it's better?

I'd be really interested - thanks.

David

Answer 1

I solve this by versioning. One version - one table row. Latest version - the row with the biggest last updated date.

Answer 2

The enterprise pattern for this is to have a amendment shadow table for every table you create to show the after-image (and possibly a before image) for all columns. You will need:

• scripts to create the amendment table
• the triggers to populate them
• and maintain the above when the table changes over time.

But for a well setup enterprise all this should already be in place.

My organisation only uses this for the following:

• An audit for dbas and support to manually determine what happened (using SQL).
• Enterprise Data Warehouse (SAS) sucks all delta's from the production systems for analysis.

We create different tables if they are needed for the operational systems themselves.

Answer 3

Just create the trigger and let the trigger to automatically tracking the change and record them in your auditing table.