Geek Answers Handbook

Is there an elegant way to track the change in all columns of a single table in SQL Server 2008

My database has a table containing 100 columns. I want to create a trigger to check for changes for each update operation.

I can think of creating an update clause for all columns, but they all look like scripts. So, is there an elegant way to do this?

+6
sql-server sql-server-2008 audit
source share
4 answers

Check Modify data collection.

Update
CDC provides tracking of all details of changes. Available since SQL Server 2008.

(Changing data capture is only available in Enterprise, Developer, and Evaluation SQL Server editions. Source: http://msdn.microsoft.com/en-us/library/bb522489.aspx )

An easier solution is Change Tracking (Sync Framework), one code4life mentioned earlier , available with SQL Server 2005.

Update2:
Related questions (with lots of sublinks):

  • History tables: pros, cons and gotchas - using triggers, sproc or at the application level History tables: pros, cons and gotchas - using triggers, sproc or at the application level
  • Suggestions for implementing audit tables in SQL Server?
    Suggestions for implementing audit tables in SQL Server?
  • Is soft removal a good idea?
    Is soft removal a good idea?
  • How do I change the MS SQL database in SVN? SQL Server Database Versions
  • Thomas Laroc. SQL Server Audit: Wizardless Magic
    http://www.simple-talk.com/sql/database-administration/sql-server-audit-magic-without-a-wizard/
+3
source share

Here is a resource on MSDN that you might find useful:

Track changes to the server database (including SQL Server 2008)

I am not sure if you are using SQL Server 2008.

+1
source share

Code generation?

Have you looked at the methods that http://autoaudit.codeplex.com/ uses?

+1
source share

Theoretically, you could use 1 trigger and check COLUMNS_UPDATED () to see which columns were changed. (not verified) Read more here

0
source share

More articles:


All Articles