• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Triggers bad for performance?

I read few yrs ago that Triggers are bad for performance and they're not recommanded in SQL Server (they're ok in Oracle).

Is this correct? My coworker wants to use a trigger and wants to put in a lot of logic in there. Logic like if-else. I think that should either be done in a stored proc or in code. Trigger should just be used to update/insert simple data...no?
0
Camillia
Asked:
Camillia
  • 3
  • 2
1 Solution
 
chapmandewCommented:
There is no yes or no answer here ...it depends.  If the triggers have some complex logic in them, then they could be bad for performance.  If they are simple, then they may not be.  Remember, they execute in the scope of the transaction that fires there, so there will always be some overhead in them, but it really depends on the situation in terms of if they are bad for performance or not.
0
 
CamilliaAuthor Commented:
>> execute in the scope of the transaction that fires there

can you explain this a little bit futher..not sure what you mean. Thanks. Kamila.
0
 
chapmandewCommented:
Sure.  So, say you run an update statement on tableA which affects 3 rows.  If tableA has a trigger on it, the update statement will only be committed upon successful completion of the work being done by the trigger.  In other words, triggers will make the transactions at least a little bit longer because the work they do is included in the transaction...does that make sense?
0
 
CamilliaAuthor Commented:
yes, thanks for youe help as always.
0
 
chapmandewCommented:
always a pleasure to help.
tim
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now