Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Trigger Execution Order Fired Under Different Events in SQL Server

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1
    • 0
    • 1.44k
    Comment on it

    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.

     

    Trigger Execution Order Fired Under Different Events in SQL Server

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: