Triggers to Execute in Pre Defined Order

  • Dinesh Asanka

    SSChampion

    Points: 11058

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/triggerstoexecuteinpredefinedorder.asp




    My Blog: http://dineshasanka.spaces.live.com/

  • Jeremy Swift

    SSC Journeyman

    Points: 83

    Big thing to watch out for if trying to use the ordering property is that replication will hijack the FIRST setting for its own trigger.

    SBO: "Replication automatically generates a first trigger for any table that is included in an immediate updating or queued updating subscription. Replication requires that its trigger be the first trigger. Replication raises an error when you try to include a table with a first trigger in an immediate updating or queued updating subscription. If you try to make a trigger a first trigger after a table has been included in a subscription, sp_settriggerorder returns an error. If you use ALTER on the replication trigger or use sp_settriggerorder to change the replication trigger to a last or none trigger, the subscription will not function correctly."

    Also modifying a trigger unsets the ordering.

    SBO: "If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute is dropped and the order value is set to None. The order must be reset by using sp_settriggerorder"

    I'm struggling with the primitive trigger capabilities of SQL Server 2005, having come from a Sybase ASA background where you have the luxury of multiple before and after triggers, each of which can be given a unique execution priority and each of which can be defined as operating at row level or trigger (ie set) level. They also have an update trigger variant which is fired only on change in one or more listed columns. All these features mean that triggers can be kept small and simple and having them they get used. I hope Microsoft are working towards similar elegance in a future release.

    Hope these thoughts enhance the collective DNA

  • elarrat

    SSC Rookie

    Points: 46

    I didn't see the table TriggerName:

    (I created it like this

    CREATE TABLE [TriggerName] (

    [Id] [int] IDENTITY (1, 1) NOT NULL ,

    [TriggerName] [varchar](21) NULL

    ) ON [PRIMARY]

    GO

     

                                     Elarrat, JIA * o Portimoteco!

                       (From Amazon Jungle - Porto de Moz-Xingu-PA-Brazil)


    Elarrat, JIA,
    o Portimoteco!

  • Jeswanth Jaishanker

    SSCommitted

    Points: 1869

    Is there any way to set order for triggers if teh same is more than 3-4 . coz i have a table which fires more than 12 triggers.

    Jeswanth

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

  • Johan Bijnens

    SSC Guru

    Points: 134286

    now that's where the cautions come in ...

    12 triggers on one object, may be a bit _to_ many

    Maybe there is a way to redesign them to less.

    The general guideline is triggers should not be order-dependant !

    Like Dinesh Asanka menetioned in this article, these settings are not scripted and are very vulnarable (e.g. alter trigger "resets" them) !

    Actualy I'm happy Dinesh shared this little way of influence we may use in strict cases.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply