Is there some database-independent way get data change notifications at the field level?

Question

Currently, using SQL Server 2005, I poll a "windows events" database to determine changes in a table called WINDOWS_EVENTS, which has a timestamp field. Looking at the timestamp I can determine if the row changed, but not what field in that row changed.

Is there some generic (i.e. database independent way) to detect field level changes? (I need it to be generic, because there's no telling what database a customer would use, so I don't want to use Notification Services or other SQL-Server-specific technology. I could use any solution, .NET, Java or any other language if that helps me solve my problem.)

Answer 1

First, I would recommend changing your table structure from something like this:

id      field1   field2   field3   field4   timestamp

into something like this:

Table 1
-------
id     fieldKey    value    timestamp
1      1           42       12:03am
2      3           'Cow'    1:45am
3      2           'Moo'    2:33am
4      4           99       3:59am

Table 2
-------
fieldKey     fieldLabel
1            Field One
2            Field Two
3            Event One
4            Event Two

Second, you can achieve what you want by copying the old value or calculating the hash of the field values (for long fields) and storing them in another field (oldField1, oldField2, etc.) along with the time stamp, then poll the table. I believe all databases have a hash function, like SHA1('xyz') or MD5('abc'), though it might have a slightly different name in each one.

Then when you poll the table for changes, you see the row that has changed, and you can do a field by field comparison to find out which field has changed. So you would compare field1 to oldField1, field2 to oldField2, field3 to oldField3, etc. For blobs, you could compare SHA1(field4) to oldField4).

Create a stored procedure trigger to copy the fields when you update the time stamp.

Another solution is to create a data change log table which only stores changes. It would be inserted into via a trigger stored procedure, and it would look like this

id    tableName       rowKey    fieldName     oldValue     newValue     timestamp
1     WINDOWS_EVENTS  42        event1        stable       crash        4:44am