SQL Server Performance

Priority of Insert triggers

Discussion in 'General Developer Questions' started by dineshasanka, Nov 30, 2005.

  1. dineshasanka Moderator

    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.

    However, I need to make sure that one trigger has higher priority than the other.

    Are these triggers running at once or one after the other.

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  2. druer New Member

    Check out: sp_settriggerorder
  3. SQLDBcontrol New Member

    Use sp_settriggerorder<br /><br />Oops, beaten to it[<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<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
  4. dineshasanka Moderator

  5. dineshasanka Moderator

  6. Adriaan New Member

    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.
  7. dineshasanka Moderator

  8. Adriaan New Member

    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.
  9. dineshasanka Moderator

    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
  10. Adriaan New Member

    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.
  11. dineshasanka Moderator

  12. FrankKalis Moderator

    quote:
    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
    That should be easy to test. I would guess that SQL Server stores this somewhere in the metadata or the properties.


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  13. dineshasanka Moderator

  14. FrankKalis Moderator

    Please share the results of yourt tests here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  15. dineshasanka Moderator

  16. SQLDBcontrol New Member

    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
  17. dineshasanka Moderator

    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
  18. SQLDBcontrol New Member

    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
  19. dineshasanka Moderator

    I didn't check the status, But it is not giving me the correct answers. When I re-run the scripts it works. SO I assumed that this not stored in the working DB

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  20. Adriaan New Member

    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)?
  21. dineshasanka Moderator

    Yes it works well for start and restating the Service. But when you do a backup that setting is missing.

    U can set all to None and then reset it.



    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  22. SQLDBcontrol New Member

    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
  23. dineshasanka Moderator

  24. dineshasanka Moderator

  25. Adriaan New Member

    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.
  26. dineshasanka Moderator

Share This Page