Trigger Performance : Is Using Trigger is a good way?

We are using SQL Server 2008 for a j2ee web application. I need to maintains the audits for updates for each column with their previous value and new value on one of my table which is very heavy, around 100 columns and 10K records and increasing rapidly on daily basis. Around 200 users work with this application and updates also occur very frequently.
I know handling it via code will require very frequent connections to database.
I want to know which is a better approach for doing this. Is handling it through code will provide a better performance than using SQL Server trigger for that.

Personally, I would use triggers for auditing.

• If they fail, then the main DML gets rolled back

• (same) The main update requires the DML to succeed

• A table will typically have several DML paths

• You may not be able to use code

• You will have direct table update that bypass code at some point

Ok, so we'll use triggers. Some notes

• Ensure they work for multiple rows: no CURSORs or loops

• Use them only for auditing: nothing else (no, say, sending emails)

• Ensure you use TRY/CATCH in the triggers: this massively improves the behaviour on rollbacks in triggers

• CASCADE foreign keys will cause child table triggers to fire: Beware nesting and recursion and don't use CASCADEs if possible

This last list also addresses sonme points in @NevilleK's answer. Triggers are one tool to use: use wisely and when appropriate and correctly.

600 updates per hour is trivial, not a problem at all for triggers (subject to cursors, loops, sending email and other stupid stuff)

StackExchange Database Administrators Q#41754, answer score: 10