Auditing - Trigger Or Stored Procedure

Hi All,

We have a requirement in the project to do auditing for some of the tables. We have come across different approaches of implementing auditing.

The following are the approaches:


Create a generic stored procedure to do auditing. Call this procedure whenever any tables that require auditing have insert/update/delete operation performed on it. Auditing and the DML operation should be part of a single transaction.
Create a generic CLR trigger to do the auditing. The CLR trigger can be attached to the tables that require auditing.
Create separate audit tables and triggers for each table which require auditing.

I would like to know whether there are any disadvantages of using triggers in production server. Could anyone please help me in identifying the best approach for implementing auditing?

Will there be any situation when the DML statements execute and the corresponding trigger fails?

Is there any performance degradation on using triggers for auditing compared to including the auditing logic implemented in the stored procedures? We have been advised not to use triggers in production environment. But we are not clear about the reason for this.

PLEASE NOTE THAT I DO NOT WANT TO REPLACE STORED PROCEDURE WITH TRIGGERS TO IMPLEMENT BUSINESS LOGIC. BUT IN THE SCENARIO THAT I DISCUSSED, I NEED TO CAPTURE THE LOG INFORMATION WHENEVE A DML STATMENT IS EXECUTED AGAINST THE TABLES. WHAT IS THE PREFERRED APPROACH HERE...USING TRIGGERS OR STORED PROCEDURES?

In general, is there any disadvantage in using triggers for auditing? Has anyone faced any issues with triggers? (Triggers not invoked during DML operations or any performance related issues) Please let us know.



Thanks,