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.

Conclusion

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)

How to capture changed data without schema modification : Part 1

Sometimes we need to capture changed row in table.  One good approach is to use change tracking option which is available in SQL Server 2008 and later. Another good solution is to use triggers on tables which linking deleted and inserted tables. But what if we have no permissions to modify database schema?

In that case we can use OUTPUT clause in INSERT OR UPDATE statement.

Here is an short example.

Let us create two table variables: @t_original  will be original (changed) table and @t_copy which will store deleted row.

Then we insert two rows into originl table. Changed rows will be inserted into table @t_copy

UPDATE  @t_original
SET     AnotherColumn = 4 ,
   Name = NULL
OUTPUT  DELETED.*
        INTO @t_copy
WHERE   id = 1 ;

Now we have both old and new rows and can use it later to compare and check differences.

SELECT  Copy.id ,
        Copy.NAME Name_Old ,
        Copy.AnotherColumn AnotherColumn_Old ,
        Original.NAME Name_New ,
        Original.AnotherColumn AnotherColumn_New
FROM    @t_copy Copy
        RIGHT OUTER JOIN @t_original Original ON Copy.id = Original.id

We will use results from those statements later to answer a question which columns were changed.