Priority of Insert triggers

Check out: sp_settriggerorder

Use sp_settriggerorder<br /><br />Oops, beaten to it[<img src='/community/emoticons/emotion-4.gif' alt='' />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by dineshasanka</i><br /><br />I need to write two insert triggers to a same table. This must be two as one as for replication and other is not for replication.<br /><br />However, I need to make sure that one trigger has higher priority than the other.<br /><br />Are these triggers running at once or one after the other.<br /><br />----------------------------------------<br /<a target="_blank" href=http://spaces.msn.com/members/dineshasanka>http://spaces.msn.com/members/dineshasanka</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com

If you put the whole processing into a single trigger, you can be pretty sure about the order in which it gets processed ...

I know, sometimes you get flexibility forced upon yourself.

I notice that there is only a "NOT FOR REPLICATION" clause, so there's no positive version.

Could it be the same as with a trigger firing on a child table when the parent table gets updated? @@Rowcount returns 0 if it's on the first line of the trigger, and deleted and inserted are both empty ...

Create a test procedure that starts a replication on the table, then you should see the 'replication trigger' code come up - check @@rowcount.

I notice that sp_settriggerorder only lets you set a first and a last trigger. If you have more triggers defined for the same event, the remaining ones get executed in undefined order (BOL).

What also remains unclear is what happens if you stop the SQL Server instance and then restart it - you probably need to run sp_settriggerorder all over again - but wait for the experts to chime in.

It is stored in the status column of the sysobjects table.

To get at it you can use the OBJECTPROPERTY function.

e.g. select objectproperty(object_id('mytrigger'), 'ExecIsFirstUpdateTrigger')

Returns true or false. There are similar property names for delete and insert triggers as well as for finding out whether it is a last trigger.

Hope that helps,

quote:Originally posted by dineshasanka

What is the issue with sp_settriggerorder. this is working fine for me. Only thing I need is to find out where it is stored this data

----------------------------------------
http://spaces.msn.com/members/dineshasanka

Karl Grambow

www.sqldbcontrol.com

Have you checked the status column for the given object on both servers? I'd be surprised if they're different values but then anything is possible I guess.

quote:Originally posted by dineshasanka

Thank you very much for the info. But whenever I do a back and restore this to another server, this settings has gone!! I had to re-run the scripts Why is that???

----------------------------------------
http://spaces.msn.com/members/dineshasanka

Karl Grambow

www.sqldbcontrol.com

Also double-check for ...
OBJECTPROPERTY(OBJ_ID('MyTrigger'), 'ExecIsTriggerDisabled') = 0
... to ignore the trigger if it is disabled.

I did a simple stop/start test on my local SQL Server instance, and afterwards a disabled trigger was still disabled, and a 'last' trigger was still 'last'. So the setting seems to be permanent.

But it doesn't say anywhere how you can undo sp_settriggerorder, to restore the undefined order. So it must be to drop and recreate the triggers?

And if you don't use sp_settriggerorder, don't triggers just fire in the order in which they were created (for the event on which they fire)?

To undo sp_settriggerorder you can use a value of 'none' for the @order parameter.

Not that this helps Dinesh though.

Dinesh, I'd check the value of the status column after you restore. Something else must be happening because the order of the trigger should definately be stored in the database so restoring it should carry over.

quote:Originally posted by Adriaan

Also double-check for ...
OBJECTPROPERTY(OBJ_ID('MyTrigger'), 'ExecIsTriggerDisabled') = 0
... to ignore the trigger if it is disabled.

I did a simple stop/start test on my local SQL Server instance, and afterwards a disabled trigger was still disabled, and a 'last' trigger was still 'last'. So the setting seems to be permanent.

But it doesn't say anywhere how you can undo sp_settriggerorder, to restore the undefined order. So it must be to drop and recreate the triggers?

And if you don't use sp_settriggerorder, don't triggers just fire in the order in which they were created (for the event on which they fire)?

Karl Grambow

www.sqldbcontrol.com

BOL has some more details that could explain how the order can get dropped inadvertently - for instance

quote:If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset with sp_settriggerorder.