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
