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.
Create a table Students
/****** Object: Table [dbo].[Students] ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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
)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]
Create two triggers which will execute after insertion transaction in Students table
CREATE TRIGGER [dbo].[FIRST_TRIGGER]
PRINT 'EXECUTION ORDER IS FIRST'
CREATE TRIGGER [dbo].[SECOND_TRIGGER]
PRINT 'EXECUTION ORDER IS SECOND'
Inserting a row in Students table
Insert into Students Select 'Keerti','Yadav','email@example.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 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.