Trigger Execution Order

Introduction 

If we have four Insert trigger in One Table, after Inserting values on it which one fire first, second and last.

To find this question answer lets taken an example.

Step-1 [ Creating Base Table and Output Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

     (EMPID    INT         NOT NULL PRIMARY KEY,

      EMPNAME  VARCHAR(50) NOT NULL);

     

IF OBJECT_ID(N'dbo.tbl_TRGACTION', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_TRGACTION];

   END

GO

CREATE TABLE [dbo].[tbl_TRGACTION]

     (EMPID    INT         NOT NULL,

      EMPNAME  VARCHAR(50) NOT NULL,

      FROMTRG  VARCHAR(50) NOT NULL);

           

Step-2 [ Now we Create 3 Insert Trigger On this Base Table named tbl_EMPLOYEE ]

--Trigger Creation   - 1    

IF OBJECT_ID(N'dbo.trg_INDERT_1', N'TR')IS NOT NULL

   BEGIN

     DROP TRIGGER [dbo].[trg_INDERT_1];

   END

GO

CREATE TRIGGER [dbo].[trg_INDERT_1] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_1' AS FROMTRG

    FROM   INSERTED;   

END        

GO

--Trigger Creation   - 2    

IF OBJECT_ID(N'dbo.trg_INDERT_2', N'TR')IS NOT NULL

   BEGIN

     DROP TRIGGER [dbo].[trg_INDERT_2];

   END

GO

CREATE TRIGGER [dbo].[trg_INDERT_2] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_2' AS FROMTRG

    FROM   INSERTED;   

END 

GO

--Trigger Creation   - 3    

IF OBJECT_ID(N'dbo.trg_INDERT_3', N'TR')IS NOT NULL

   BEGIN

     DROP TRIGGER [dbo].[trg_INDERT_3];

   END

GO

CREATE TRIGGER [dbo].[trg_INDERT_3] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_3' AS FROMTRG

    FROM   INSERTED;   

END

GO

When the each Trigger fires it insert records on table tbl_TRGFIRE. By which we can understand which one fire first and so on.

Step-3 [ Insert a Records on Base Table and Observe the tbl_TRGFIRE ]

INSERT INTO tbl_EMPLOYEE

       (EMPID, EMPNAME)

VALUES (1, 'Joydeep Das'); 

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

SELECT * FROm [dbo].[tbl_TRGACTION]  

EMPID       EMPNAME                FROMTRG

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

1           Joydeep Das            trg_INDERT_1

1           Joydeep Das            trg_INDERT_2

1           Joydeep Das            trg_INDERT_3

(3 row(s) affected)

Here the Trigger fire on FIFO Basis. That means which one executes first is fire first.

Step-4 [ Alter Trigger at Reverse Order and Observe the tbl_TRGFIRE ]

-- NOW WE Altering The Trigger Sequence [ Recerse Order ]

ALTER TRIGGER [dbo].[trg_INDERT_3] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_3_ALTER' AS FROMTRG

    FROM   INSERTED;   

END

GO

ALTER TRIGGER [dbo].[trg_INDERT_2] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_2_ALTER' AS FROMTRG

    FROM   INSERTED;   

END

GO

ALTER TRIGGER [dbo].[trg_INDERT_1] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_1_ALTER' AS FROMTRG

    FROM   INSERTED;   

END   

GO

Step-5 [ Insert a New Records on Base Table and Observe the tbl_TRGFIRE ]

INSERT INTO tbl_EMPLOYEE

       (EMPID, EMPNAME)

VALUES (2, 'Chandan Bannerjee');

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

SELECT * FROm [dbo].[tbl_TRGACTION] WHERE EMPID=2;

EMPID       EMPNAME                       FROMTRG

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

2           Chandan Bannerjee             trg_INDERT_1_ALTER

2           Chandan Bannerjee             trg_INDERT_2_ALTER

2           Chandan Bannerjee             trg_INDERT_3_ALTER

(3 row(s) affected)

Same Output as FIFO

Step-6 [ Insert a New Records on Base Table and Observe the tbl_TRGFIRE ]

Now we alter only Second Trigger and Execute it Again

-- Now We Alter Only Second Trigger

ALTER TRIGGER [dbo].[trg_INDERT_2] ON [dbo].[tbl_EMPLOYEE]

AFTER INSERT

AS

BEGIN

    INSERT INTO [dbo].[tbl_TRGACTION]

        (EMPID, EMPNAME, FROMTRG )

    SELECT EMPID, EMPNAME, 'trg_INDERT_2_ALTER_2nd Time' AS FROMTRG

    FROM   INSERTED;   

END

GO

INSERT INTO tbl_EMPLOYEE

       (EMPID, EMPNAME)

VALUES (4, 'Sangram Jit Bhattacharya');

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

SELECT * FROm [dbo].[tbl_TRGACTION] WHERE EMPID=4;

EMPID       EMPNAME                             FROMTRG

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

4           Sangram Jit Bhattacharya            trg_INDERT_1_ALTER

4           Sangram Jit Bhattacharya            trg_INDERT_2_ALTER_2nd Time

4           Sangram Jit Bhattacharya            trg_INDERT_3_ALTER

(3 row(s) affected)

Step-7 [ Conclusion by Observing Result ]

Trigger	Execution Order
1.    Creating Trigger trg_INDERT_1 2.    Creating Trigger trg_INDERT_2 3.    Creating Trigger trg_INDERT_3	Order of Execution 1.    trg_INDERT_1 2.    trg_INDERT_2 3.    trg_INDERT_2
1.    Alter Trigger trg_INDERT_1 2.    Alter Trigger trg_INDERT_2 3.    Alter Trigger trg_INDERT_3	Order of Execution 1.    trg_INDERT_1 2.    trg_INDERT_2 3.    trg_INDERT_3
1.    Alter Trigger trg_INDERT_2	Order of Execution 1.    trg_INDERT_1 2.    trg_INDERT_2 3.    trg_INDERT_3

What is the Process of Altering the Trigger Execution Order

If Multiple Trigger exists within the same Table Objects we can set which trigger can execute first and which trigger can execute last. In between them we are unable to set the execution order of trigger.

We can do this by using sp_settriggerorder system stored procedure

exec sp_settriggerorder @triggername = ,

          @order = [FIRST|LAST|NONE],

          @stmttype = [INSERT|UPDATE|DELETE|],

          @namespace = [DATABASE|SERVER|NULL]

•  @triggername  it's the trigger being ordered.
•  @order indicates whether the trigger should fire FIRST or LAST. If NONE is specified, then no order is enforced.
• @stmttype indicates the trigger type i.e. whether it's an INSERT trigger, for instance.
• @namespace  indicates whether a DDL trigger was created on the database or on the server. If set to NULL, it indicates that the trigger is a DML trigger

Example

exec sp_settriggerorder

     @triggername = 'trg_INDERT_3',

     @order = 'first',

     @stmttype = 'insert',

     @namespace = null

    

exec sp_settriggerorder

     @triggername = 'trg_INDERT_1',

     @order = 'last',

     @stmttype = 'insert',

     @namespace = null 

Hope you like it.

Posted by: MR. JOYDEEP DAS