Presents your 
SQL SERVER E-NEWSLETTER for June 17, 2003

<------------------------------------------->

USE SP_SETTRIGGERORDER TO SET AFTER TRIGGERS

If you have multiple AFTER triggers set on your table, you have a
limited amount of control over them. As a rule, SQL Server 2000 only lets you 
set the first and last AFTER triggers. Triggers that are in between first
and last are fired in an undefined order. To set AFTER triggers, you 
can use sp_settriggerorder.

In the example below, we'll set MyTrigger to be the first trigger on 
the UPDATE statement:

sp_settriggerorder @triggername= 'MyTrigger', @order='first', @stmttype
=
'UPDATE'

You can only have one first and last trigger for each INSERT, UPDATE,
and DELETE statement on a single table. If you've already created a trigger
and designated it as first, then use the ALTER TRIGGER statement to undo
its first designation, and use sp_settriggerorder to set it.

When you use ALTER TRIGGER, the first or last attribute set on a trigger
is dropped, and its value is replaced by none, wiping out its setting.
Now you're free to set it to the order that you want. However, if 
you're going to set first and last triggers for multiple statement types, 
you'll need to execute sp_settriggerorder for each statement type.

Remember that you have to define the trigger for the statement type
before you can designate it as the first or last trigger to fire.

J.E. Harvey, MCSD, MCDBA, has been tinkering, writing, and consulting
about technology for more than two decades.

----------------------------------------