Friday 28 February 2014

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

No comments:

Post a Comment