The automatic execution of stored programs when a specific event occurs are triggers. Triggers are database object binded to a table and are called implicitly. They find their usage while accessing and checking data before and after DDL and DML queries make modifications thus providing data integrity.

In this tutorial, we will learn about "The Trigger Execution Order Fired Under Different Events in SQL Server".  

SO, let's begin:

Triggers are fired in the following events:

• Inserting data into table
• Updating table data record 
• Deleting table data record

For Insert, Delete and Update transaction more than one trigger can be created. But triggers do not have specific execution order. Triggers execution are performed randomly. But sometimes requirement arises when two triggers associated with a table must execute in specific order for same table action.

Example:

Create a table Students

USE [Student_management]
GO

/****** Object:  Table [dbo].[Students] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Students](
	[Fname] [nvarchar](max) NOT NULL,
	[Lname] [nvarchar](max) NOT NULL,
	[Email] [nvarchar](max) NOT NULL,
	[student_id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_dbo.Students] PRIMARY KEY CLUSTERED 
(
	[student_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Create two triggers which will execute after insertion transaction in Students table

Trigger 1

CREATE TRIGGER [dbo].[FIRST_TRIGGER] 
ON [dbo].[Students]
AFTER INSERT  
  
AS  
BEGIN  
  
    PRINT 'EXECUTION ORDER IS FIRST'  
END 
GO

Trigger 2

CREATE TRIGGER [dbo].[SECOND_TRIGGER]  
ON [dbo].[Students]  
AFTER INSERT  
  
AS  
BEGIN   
    PRINT 'EXECUTION ORDER IS SECOND'  
END

Inserting a row in Students table

Insert into Students Select 'Keerti','Yadav','k@gmail.com'

After insertion triggers for insertion FIRST_TRIGGER and SECOND_TRIGGER will fire and produces following output.

In above output, it can be seen that trigger execution does not depend on the order of trigger creation. Its execution is random. But execution of triggers can be defined via system procedure "sp_settriggerorder"

sys.sp_settriggerorder have following parameters :-

@triggername    nvarchar(517),    -- name of the trigger 
@order            varchar(10),    -- first, last, or none
@stmttype        varchar(50),    -- statement (event) type
@namespace        varchar(10) = NULL        -- database, server or null

@triggername: It defines the name of the trigger to which sys.sp_settriggerorder belongs.
@order: It defines trigger execution order which can be any of the following

•  first : to be exeuted first
•  last : to be exeuted last
•  none : random execution

@stmttype: It defines trigger type for e.g : Insert,Delete,Update etc.
@namespace: It indicates whether the creation of DDL trigger was on server or database. For DML trigger this value is null.

Now define the order of above two triggers.

EXEC sys.sp_settriggerorder @triggername = 'FIRST_TRIGGER',  
   @order = 'FIRST',  
   @stmttype = 'INSERT',  
   @namespace = NULL
   

EXEC sys.sp_settriggerorder @triggername = 'SECOND_TRIGGER',  
   @order = 'LAST',  
   @stmttype = 'INSERT',  
   @namespace = NULL   

Now again inserting a row in Students table will provide following output :-

From above output, it can be seen that execution of trigger is ordered. But limitation with above trigger execution is that @order = First and @order = Last can be associated only with one trigger i.e if a trigger is defined on a table for some event with  @order = First then a new trigger for same table and event cannot be defined with @order = First otherwise it throws an error. This restriction is also applicable to the last trigger.