sql server Trigger Implicit order?

Question

I have 2 Triggers on a table : Tr1 , Tr2 .

I didn't set order to any.

my question :

If I set to Tr1 sp_settriggerorder with "first" value.

Does it say implicitly that Tr2 will be the second one automatically?

Or should I also use sp_settriggerorder with "last" value to the Other Trigger ?

What would be the only sensible implementation of this?

Martin Smith
– Martin Smith

2011-11-21 13:32:05 +00:00
Commented Nov 21, 2011 at 13:32 — Martin Smith
– Martin Smith, Commented Nov 21, 2011 at 13:32

RThomas · Accepted Answer · 2011-11-22 01:20:21Z

1

As Martin Smith alludes in his comment. If you only have two triggers and one has been designated as first. Then the remaining trigger has to fire second. That is proper application of ordering when you have two triggers.

For a very thorough explanation of trigger ordering read this article

but in summary...

If you have 2 triggers you only need to designate a first OR a last to ensure a firing order.
If you have 3 triggers you must designate BOTH a first and a last to ensure firing order. The third trigger won't have an order but since it's the only one left it automatically gets fired 2nd.
If you have four or more triggers, you may designate a first and a last but the others will fire in an arbitrary order somewhere in the middle. You may also want to have a 'pshrink' on retainer if you regularly have four or more triggers on a table.

edited Nov 22, 2011 at 1:20

answered Nov 22, 2011 at 0:33

RThomas

10.9k2 gold badges50 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql server Trigger Implicit order?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related