Tracking a Change on a Column

Question

I recently ran across a very interesting problem involving database design. I have changed the table names to simplify the problem, so let me describe it as such: I have 2 tables, fruit and vegetable each stores whether or not a fruit or vegetable is tasty.

Now lets say that someone keeps changing the IsTasty setting through the UI of my application and management is demanding the ability to see when someone changed it last and who. The tricky part here is although we are ignoring the other data on the table, there is other data, and we don’t want to track when any data on the table was changed, just this one column.

What is the best way to solve this problem?

I have a description of the problem with ER diagrams here:

Answer 1

If you are using SQL Server 2008, have you taken a look at CDC (Change Data Capture)?

Answer 2

In a Trigger, there is a way to see which columns were modified..

Answer 3

I like the way the acts_as_versioned plugin does it in Rails. It's closest to your solution 2 with an additional version number field. You basically have your fruits table and your fruits_versions table. Every time a row in fruits is updated, you insert the same data in the fruits_versions table, with an incremented version number.

I think it's more extensible than you solution 3 approach if you ever want to add more fields to the tables or track additional values. Solution 4 is sort of a non-relational solution, you could probably keep an audit log like that.

Another approach, as opposed to keeping the versions, is to keep track of the changes, like subversion or a version control system does. This can make it easier if you often need to know if something changed from a to b, versus just what it changed to. I guess that means the real answer is "it depends" on how the data will be used.

Answer 4

Using SQL Server 2005 and up you can create a trigger with an if statement like this:

IF UPDATE(IsTasty)
BEGIN
        Insert INTO Log (ID, NewValue) VALUES (@ID, @NewValue)
END

Answer 5

One way to do this is to add triggers to those tables. In the triggers check to see if the column you are interested has changed. If it has changed, insert a row into another table that tracks changes. The change tracking table might want to store data like the name of the column that was changed, the previous value, the new value and the date of the change.

Answer 6

Fo SQL Server, I use AutoAudit to generate the triggers. The audit table contains the history and views can be used to show the changes (AutoAudit makes views for deleted rows automatically).