Specify Execution order of multiple INSERT trigger...

  • Hi everyone,

    I need to find out if it is possible to specify the order of execution of triggers when having multiple trigger of the same kind on the same table. And if it is, how?

    We are hosting a third party database and I would like to add an INSERT trigger on one of the table without altering the existing INSERT trigger. The tricky part is that I want to make sure my trigger will be executed after the existing one, not before.

    If we take the below code for example, how can I ‘force’ trg_Test to execute after trg_Test2??

     Thanks!

     

    CREATE TABLE [Test] (

                    [RowID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

                    [Col1] [float] NOT NULL ,

                    [Col2] [float] NULL ,

                    CONSTRAINT [PK_Test] PRIMARY KEY  CLUSTERED

                    (

                                    [RowID]

                    )  ON [PRIMARY]

    ) ON [PRIMARY]

    GO

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

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

     

    CREATE TRIGGER trg_Test2  ON [dbo].[Test]

    after INSERT

    AS

     

    update Test set

                    Col1 = t.Col1 * 10

    from Test t

                    inner join inserted i on i.RowID = t.RowID

     

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

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

     

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

     

    CREATE TRIGGER trg_Test  ON [dbo].[Test]

    after INSERT

    AS

     

    update Test set

                    Col2 = t.Col1 * 10

    from Test t

                    inner join inserted i on i.RowID = t.RowID

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

     

  • Check out sp_settriggerorder in the BOL.  You can set only the first and last triggers.

  • thanks!

  • or modify current trigger to call a stored procedure which would have the code for all your triggers in the order you want them

  • make sense, great idea as well

     

     

    thank you very much

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

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