Is there an elegant way to track the modification of all columns of one table in SQL Server 2008

Question

There is a table in my database containing 100 columns. I want to create a trigger to audit the modification for every update operation.

What I can think is to create the update clause for all columns but they are all similar scripts. So is there any elegant way to do that?

Answer 1

Check Change Data Capture

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

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

More lightweight solution is Change Tracking (Sync Framework), the one code4life mentioned before, available since SQL Server 2005.

Update2:
Related questions (with a lot of sublinks):

• History tables pros, cons and gotchas - using triggers, sproc or at application level History tables pros, cons and gotchas - using triggers, sproc or at application level
• Suggestions for implementing audit tables in SQL Server?
  Suggestions for implementing audit tables in SQL Server?
• Are soft deletes a good idea?
  Are soft deletes a good idea?
• How do I version my MS SQL database in SVN? Versioning SQL Server database
• Thomas LaRock. SQL Server Audit: Magic without a Wizard
  http://www.simple-talk.com/sql/database-administration/sql-server-audit-magic-without-a-wizard/

Answer 2

There's this resource on MSDN which you might find helpful:

Tracking Changes in the Server Database (including SQL Server 2008)

I'm not sure if you're using SQL Server 2008 though.