March 19, 2006 at 5:12 pm
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??
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
March 20, 2006 at 9:02 am
Check out sp_settriggerorder in the BOL. You can set only the first and last triggers.
March 20, 2006 at 9:16 am
thanks!
March 21, 2006 at 12:00 am
or modify current trigger to call a stored procedure which would have the code for all your triggers in the order you want them
March 21, 2006 at 7:51 am
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