CODEWITHSUNDEEP
sql-server-2005triggers
Royi Namir
Royi Namir

Reputation: 148524

sql server Trigger Implicit order?

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 ?

Upvotes: 0

Views: 111

Answers (1)

RThomas
RThomas

Reputation: 10882

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.

Upvotes: 1

Related Questions