Steve Jones - SSC Editor (4/21/2014)
CDC is an enterprise only feature as well. That can be an issue.The CDC stuff can get complex and be cumbersome to deal with, especially with DR environments. Make sure you practice working with it. It can also potentially be a performance issue, as Jeff noted.
If you are looking to write audit triggers, you can selectively check which items were changed with the UPDATE() function or use a CASE to do comparisons between inserted and deleted and then insert a value. I might do the latter, having SQL do comparisons and insert null for those columns not changed.
Do be careful inserting NULL into an audit table for columns that don't change. I did this exact scenario at one point and it really bit me hard. It was impossible to tell when a value was set to NULL that previously had a value. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/