CODEWITHSUNDEEP
c#sqlsql-server-2005methodstriggers
Kashif
Kashif

Reputation: 14440

Database trigger or a common method in code?

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.

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?

Upvotes: 4

Views: 2407

Answers (4)

Ali
Ali

Reputation: 1210

it is never too late for such questions ,
in General , triggers reduce the round trip of your DB and code ,
in your case , to do this in C# you will need 2 trips for each action ,one for the action (Insert) and one for the log action , and of course you need to do a lot of handling for exceptions in your code so if the record is not inserted you handle this and also you log different action of failure
as trigger ,you send the data once to the server and all actions and handling are done there with no extra connections
this is useful specially now that every thing is shared and connections polls are limited .

Upvotes: 0

Dheer
Dheer

Reputation: 4076

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

Upvotes: 5

SQLMenace
SQLMenace

Reputation: 135111

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

Upvotes: 1

Ashley
Ashley

Reputation: 129

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.

Upvotes: 2

Related Questions