Introduction
A trigger is a database object. A trigger is a special type of Stored Procedure that is automatically executed. We can create a DML, DDL, or logon trigger in SQL Server. Data Manipulation Language (DML) triggers execute when someone attempts to modify data through a DML event. DML events are INSERT, UPDATE, or DELETE statements on a view or table.
The same as Data Definition Language (DDL), triggers execute in response to a variety of DDL events. These events are CREATE, ALTER, and DROP statements and certain system Stored Procedures that perform like DDL operations. A Logon Trigger fires in response to the LOGON event raised when the user session is started.
Problem statement
Suppose, I have two triggers, Trigger A and Trigger B, define on my database table that is fire on the table for some action (INSERT, UPDATE, or DELETE). My Business Logic enforces me, Trigger A fire first. In another word, I want to fire "trigger-A" before "trigger-B". Can we set a DML trigger (AFTER trigger) firing order in SQL Server? If yes then how can we manage the trigger-firing order?
Solution
Multiple Triggers on a Database table for the same action are not fired in a guaranteed order. It is possible to set the firing order for two triggers (AFTER Trigger) using the system store procedure "sp_settriggerorder". This System Stored Procedure cannot be used with an INSTEAD OF TRIGGER and it throws an error if we are trying to set the same order on these triggers.
Syntax
sp_settriggerorder @triggername , @order
@stmttype, @namespace
Parameter Description
-
@triggername
This is a name of a trigger of which the order is to be set. Here the trigger name is the schema name. This procedure returns an error if the trigger does not exist in the schema or the trigger type is INSTEAD OF. The Trigger Schema cannot specify DDL or LOGON triggers.
-
@order
This is the value of the new order of the triggers. This parameter is VARCHAR (10). Possible values of this parameter are First, Last, and None.
| Order Value |
Description |
| First |
Set trigger on First Order |
| Last |
Set trigger on Last order |
| None |
Undefined order. |
-
@stmttype
Statement Type. This is VARCHAR (50). This is a SQL Statement that fires the trigger like INSERT, UPDATE, DELETE, and so on.
-
@namespace
It is used when the trigger is a DDL type. It specifies whether the trigger name is created within the database or the SQL Server scope. Possible values of this parameter are "DATABASE", "SERVER" or "NULL". If the trigger is created for the LOGON trigger then "SERVER" must be specified.
There is only one First and one Last trigger for each statement type per table for a DML trigger. If the First trigger is already defined on the table for any statement type (DATABASE or SERVER) then we cannot create a new trigger as First for the same table and for the same statement type. The same thing is applied to the Last trigger too.
If the DDL trigger with any statement type (DATABASE or SERVER) is on the same event then we can specify that both triggers are a First or Last trigger. However, a SERVER-scoped trigger is always fired first.
Example
I have a table called "EmployeeMaster" that has two AFTER INSERT triggers. My business logic specifies that trigger 1 must always be fired before trigger 2.
Let's create an example step-by-step
Crate EmployeeMaster table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeMaster]') AND type in (N'U'))
DROP TABLE [dbo].[EmployeeMaster]
GO
CREATE TABLE [dbo].[EmployeeMaster](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeCode] [varchar](10) NULL,
[EmployeeName] [varchar](100) NULL,
CONSTRAINT [PK_EmployeeMaster] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Create TriggerOrderTest table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TriggerOrderTest]') AND type in (N'U'))
DROP TABLE [dbo].[TriggerOrderTest]
GO
CREATE TABLE [dbo].[TriggerOrderTest](
[TriggerName] [varchar](50) NULL,
[Type] [varchar](50) NULL,
[Fireorder] [varchar](50) NULL
) ON [PRIMARY]
Create two Triggers on the EmployeeMaster Table for INSERT ("Trigger A" and "Trigger B").
In these triggers, I am just inserting a new record in the TriggerOrderTest table with the trigger name type and firing order.
--Create AFTER Trigger on EmployeeMaster table for INSERT (Trigger A)
CREATE TRIGGER dbo.trgInsertOnEmployeeMaster1
ON dbo.EmployeeMaster
AFTER INSERT
AS
BEGIN
INSERT INTO [dbo].[TriggerOrderTest] ([TriggerName],[Type],[Fireorder])
VALUES ('trgInsertOnEmployeeMaster1','DATABASE','First')
END
--Create AFTER Trigger on EmployeeMaster table for INSERT (Trigger B)
CREATE TRIGGER dbo.trgInsertOnEmployeeMaster2
ON dbo.EmployeeMaster
AFTER INSERT
AS
BEGIN
INSERT INTO [dbo].[TriggerOrderTest] ([TriggerName],[Type],[Fireorder])
VALUES ('trgInsertOnEmployeeMaster2','DATABASE','Last')
END
Set triggers order
EXEC sp_settriggerorder 'dbo.trgInsertOnEmployeeMaster1', 'First', 'INSERT'
EXEC sp_settriggerorder 'dbo.trgInsertOnEmployeeMaster2', 'Last', 'INSERT'
Insert some test data into the EmployeeMaster table
INSERT INTO [dbo].[EmployeeMaster] ([EmployeeCode],[EmployeeName])
VALUES('E0011', 'Jigneh Trivedi')
Output or Result
Now the trigger firing order has been corrected.
SELECT * FROM TriggerOrderTest
Limitation of Triggers in SQL
If we have more than two triggers then we can only specify the FIRST and LAST trigger firing order. So using this feature we can order up to three triggers on the table for the same action. If we have 4 or more triggers (on the same action) on the database table, then we can only set the trigger order FIRST and LAST, another two are fired randomly (there is no guaranteed order for the other two triggers).
This feature is not useful INSTEAD OF triggers.
Conclusion
Using the System Stored Procedure "sp_settriggerorder", we can set the firing order of a trigger with certain limitations. I hope you like it.
Reference
Join the conversation! Your thoughts help the community grow.