Thursday, 24 March 2011

How to capture changed data without schema modification : Part 2

In previous part I showed how to store changed rows. Now we will store changes in database in separate table 

Main idea how to get columns names is to use XML: at first, we create an XML from joined together old and new rows, and then we extract both old and new value as well as column name and write this data to audit table. To detect NULL value, we should use XSINIL argument of FOR XML clause.

Now we have in our audit table all necessary information: id, changed column name and both new and old values.

In this two-part article I tried to explain how is possible to capture data change even without data scheme modification. You can download full script from link below and change tables names to real. If you have any useful suggestion, please tell me.

How_to_capture_changed_columns.sql (4Kb)

No comments:

Post a Comment