Comparing Values Within Same Record 1

[PGO01]

I've got some audit tables populated by triggers - each audit table has an Inserted_xxx and Deleted_xxx column for each column in the original table.

Is there a good way to select the Inserted_XXX and Deleted_XXX columns only where they are different?

So if I have a table with 20 columns I will have an audit table with 40 columns, 20 prefixed with Inserted_ and 20 prefixed with Deleted_. If only 1 of the columns is changed in the source table, all old values and new values are propagated to the audit table, and I then want to have a view/function/proc to show me just the old and new values where there is a difference. (Along with the column name).

Ultimately I want the end result put into a varchar(max) variable.

Any help/suggestions are appreciated.

 

[markros]

Check COLUMNS_UPDATED() and UPDATE() functions in BOL.

See also

http://articles.techrepublic.com.com/5100-10878_11-6175865.html

 

[PGO01]

Never heard of that before, thank you.

 

[markros]

You're welcome