>It's easy to drop the trigger or disable it and then you will have no audit. If you know that updates can only be done using this particular SP and you can encapsulate the logic in there - use that instead of triggers.
>
>There are some other concerns in regards to triggers, but I think the above is enough for a quick answer.
>
>Except it's not a valid answer. :)
>
>#1, the only individual(s) who should be able to drop or disable a trigger are people with sufficient rights....and those people could do the same to any other stored procedure. This argument doesn't hold water.
>
>#2, using OUTPUT as the basis for audit trail processing is risky. It would require ALL updates to funnel through that stored procedure. In certain closed environments, that might be OK - but in many environments it's unrealistic to expect. You could have external jobs that perform updates outside the proc. And OUTPUT doesn't protect against DBAs that might perform manual updates (which DOES happen).
>
>The 2 best practices for audit trail processing are triggers, or (in SQL 2008) Change Data Capture. They will fire regardless of the initial mechanism that performed the update.
>
>OUTPUT is great for immediate feedback for CRUD operations....but is very questionable to use as the basis of a full-blown audit trail environment.

It's a complex debatable topic. In MSDN T-SQL forum there are different opinions on this. I gave you Kalman's Toth opinion and reasoning.
I agree in regards to CDC, but it comes with a price. Here is a good blog on how to set CDC up
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/change-data-capture-sql-server-08