Database trigger or a common method in code?

Question

I have a table where I want to Log activities of some part of my application. A record will be inserted (may be updated in future) in this table when record inserted/updated in some other table.

E.g.

• If record is inserted in Orders table an entry will be inserted in Log table.
• If record is inserted in Booking table an entry will be inserted in Log table.
• if record is updated in Customers table an entry will be inserted in Log table if Log table does not have an entry for this customer.

etc..

Should I use Triggers on these tables to add records in Log table or should I have a common method in my code and call that method whenever insert/update activity occurs?

I have to do this activity on some part of my applications so there can be more than 20 tables on which I will be adding trigger or from couple of different locations from where I will call method.

I am using SQL Server 2005 and C#

What is better, Trigger or A method?

Answer 1

As this seems an important task I would use triggers inside the RDBMS to ensure that not only your application causes the logs to be created.

Answer 2

In case someone has the ability to update the database without your app by using TOAD, SSMS, Query Ananlyzer etc tec, a trigger would be better

Answer 3

Method is better option than Trigger. Triggers are generally - performance heavy - Less visible in the code, ie are hidden away - More difficult to debug & Maintain. - Limits on values to be passed to the log table

A method would give you lots of advantages in terms of optimizing the code, and extending the logic and easier to maintain