Is their 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

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.

Answer 2

Theoretically, you can use 1 trigger and check COLUMNS_UPDATED() to know which columns has changed. (not be tested) See more here

Answer 3

Check Change Data Capture

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

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 http://stackoverflow.com/questions/1250507/history-tables-pros-cons-and-gotchas-using-triggers-sproc-or-at-application-l/1250541#1250541
• Suggestions for implementing audit tables in SQL Server?
  http://stackoverflow.com/questions/3823/suggestions-for-implementing-audit-tables-in-sql-server
• Are soft deletes a good idea?
  http://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea
• How do I version my MS SQL database in SVN? http://stackoverflow.com/questions/173/how-do-i-version-my-ms-sql-database-in-svn
• 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 4

Code generation?

Have you looked at the techniques which http://autoaudit.codeplex.com/ uses?