Patterns for implementing field change tracking.
- by David
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