sql server 2005 triggers

Question

sql server 2005 triggers

ericwenger1

Hall of Fame

Points: 3436
More actions
October 26, 2010 at 7:16 am

#143318

I've dealt with triggers in the past but this particular table has six after update triggers on it. Is there any performance degredation for having all of these triggers? I know that part of the answer would be the logic within the triggers, but, I have always just had one after update, trigger to a table.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Animal Magic SSChampion Points: 13964 More actions · Answer 1

you have kind of answered your own question. the answer is in the logic of the trigger. so if there is an insert command on each trigger, say into an audit table, then your performance impact is that of 6 inserts. You could bundle all the logic into the same trigger and it would effectively do the same thing.

certinaly no harm in having more than one at all 🙂

GSquared SSC Guru Points: 260824 More actions · Answer 2

There almost has to be at least a little performance impact, simply because of having to call each trigger causing memory and CPU use. That's probably trivial.

The main thing that would concern me with that scenario would be sequence of execution. If it matters at all, I'd consolidate into a single trigger.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 734764 More actions · Answer 3

Each trigger adds to the transaction time since it is included in the transaction that is doing the update. That can impact performance as locks are held longer to complete the total execution of the trigger.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Steve Jones - SSC Editor (10/26/2010)
Each trigger adds to the transaction time since it is included in the transaction that is doing the update. That can impact performance as locks are held longer to complete the total execution of the trigger.

Yes, but that's pretty much going to be true regardless of whether it's six triggers or one bigger trigger. Assuming, of course, that the logic in the triggers is all actually necessary.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 734764 More actions · Answer 5

I would say that Gus' suggestion of one big trigger allows you to see all the code, and perhaps optimize it. You might have a trigger designed for a certain circumstance, but there's no need to even check for it if another trigger detects a different circumstance. Minor savings, but this can add up.

ericwenger1 Hall of Fame Points: 3436 More actions · Answer 6

Thanks for the info. I agree, if I had written this, it would be all in one trigger. That way your not calling six different triggers to do an after update and it would be easier to look at the contents of the code if it were all in one trigger. That's what I was thinking before this post.

Thanks again!