SQL SERVER – 2005 Understanding Trigger Recursion and Nesting with examples

Trigger events can be fired within another trigger action. One Trigger execution can trigger even on another table or same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER. Nested triggers SQL Server supports the nesting of triggers up to a maximum of 32 levels. Nesting means that when a trigger is fired, it will also cause another trigger to be fired. If a trigger creates an infinitive loop, the nesting level of 32 will be exceeded and the trigger will cancel with an error message. Recursive triggers When a trigger fires and performs a statement that will cause the same trigger to fire, recursion will occur.

Disable Nesting/Recursing Triggers
Following script will stop executing all the nested triggers.
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

There is also alternate way to stop Trigger Recursion

ALTER DATABASE
databasename
SET RECURSIVE_TRIGGERS ON | OFF

Restrict Trigger Nesting to certain level
Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator
Next Post
SQL SERVER – Math Functions Available in SQL Server

Related Posts

28 Comments. Leave new

  • HOw to use trriger in sql server?
    please as quick as possible

    Reply
  • What is Trriger explined?
    What is Indexes explined?

    Reply
  • Hello,

    I just wanted to thank you for the article, it put me out of big troubles with a trigger I was creating.

    Keep helping people like that!!!!!!!! ;)

    Reply
  • Hello ,

    I have a INSERT/DELETE/UPDATE trigger on my master table which stores information about all the users.

    When I am updating this master table with a query like Below

    Update tblUserGeneral set Loc=2 where LearnerId in (’12’,’34’,’56’,’89’)

    The trigger gets fired for the last Learnerid i.e. 89 but not for all the learnerids in where condition.

    Why this happens? any solutions?

    Let me know if more information is requuired from my side.

    Reply
    • Even when many rows are updated, it’s a single update statement. That’s why the trigger is executed just once. The INSERTED and DELETED will contain all the rows modified during the update statement.

      Reply
  • From what I understand an after trigger will only fire “after” a query completes successfully this means after all rows are effected, you need to take this into account when writing a trigger that deals with multiple row changes. you can always conditionalize on the @@ROWCOUNT variable if you need to have different trigger functionality defendant on whether only one row or multiple rows where effected. the inserted and deleted tables hold the data for all the rows effected not just the last.

    Reply
  • Dave, excellent information as usual.

    I want to execute two triggers simultaneously from one table. Both triggers do different things and need to work independently of each other i.e the first trigger does not need to fire the second trigger (no nesting involved). On a table insert, both triggers should fire.

    Is this possible?

    Reply
  • I am trying to nest stored procedures within SQLServer 2005, and I am getting the following error:

    An INSERT EXEC statement cannot be nested.

    I build a table and return it in one stored procedure; which is called by another stored procedure and stored in a temp table (see below).

    INSERT INTO #EndPolicies EXEC [dbo].[stpGetExposure] @SnapshotTable,@StartDate,@PolicyType,@Company,@Territory

    Where stpGetExposure returns a table.

    Any help would greatly be appreciated.
    Thanks

    Joe Castelli

    Reply
  • and stored in a temp table

    Reply
  • Rex the Strange
    June 7, 2009 10:10 pm

    I learnt the hard was (unconfirmed, but it happened to me) that the recursive_triggers option is off by default.

    Watch out for that one!

    rts

    Reply
  • Thats right.
    Recursive_Triggers option is set to OFF by default.
    Nested Triggers option is set to ON by default.
    With the above settings nested triggers will work but not recursive ones.

    Reply
  • Hello sir.

    I m Using trigger for reflect changes of Insert,Updateand Delete

    For Insert i Using
    if exists(select * from inserted)
    Begin
    /insert into another table
    End
    for delete i using
    if exists(select * from deleted)
    Begin
    /insert into another table
    End
    and for update
    if exists(select * from inserted) and exists(select * from inserted)
    Begin

    End

    but at the time of Updation Three Operations Of Delete,Insert and Update are Fired i Want only Update row to Add in Another table
    How to do that Pls. Help.

    Reply
    • Use

      If update(col)

      in for update option

      Reply
    • Brian Tkatch
      April 9, 2010 5:49 pm

      What type of TRIGGER are you using?

      You should be able to specify AFTER INSERT, then another TRIGGER as AFTER DELETE, and so on.

      Reply
    • /*IN THIS EXAMPLE WE ARE USING ALL OPTION IN ONE TRIGGER, YOU NEED TO DECLARE ALL VAR. FOR ALL COLUMNS OF MASTER TABLE FOR INTIALIZING RECORDS FROM INSERTED/DELETED VIRTUAL TABLES AND MUST ISSUE A COMMAND FOR UPDATE/INSERT/DELETE FOR COMMIT IT TO MASTER TABLE ELSE IT WILL REFLECT ONLY IN BACKUP FILE I.E. EMP_BK BECAUSE WE ARE USING INSTEAD OF TRIGGER FOR WHICH WE NEED TO PASS ANOTHER CONFIRMATION COMMANDS INSIDE THE TRIGGER*/
      ————–
      Alter trigger trg_insteadof_emp on dbo.emp
      instead of INSERT,DELETE,UPDATE —USING ALL OPTIONS AT ONE GO
      as
      begin
      declare @eno int
      declare @enm varchar(10)
      declare @sal int

      IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
      BEGIN
      select @eno=I.empno from INSERTED I
      select @enm=I.ename from INSERTED I
      select @sal=I.sal from INSERTED I

      insert into emp_bk select @eno,@enm,@sal,’Record Updated’,getdate()
      print ‘Record Updated in Master Table – instead of update trigger raised’
      END
      ELSE IF EXISTS(SELECT * FROM INSERTED)
      BEGIN
      select @eno=I.empno from INSERTED I
      select @enm=I.ename from INSERTED I
      select @sal=I.sal from INSERTED I

      insert into emp_bk select @eno,@enm,@sal,’New Record Inserted’,getdate()
      print ‘Record Inserted to Master Table – instead of Insert trigger raised’
      END
      ELSE
      BEGIN
      select @eno=d.empno from deleted d
      select @enm=d.ename from deleted d
      select @sal=d.sal from deleted d

      insert into emp_bk select @eno,@enm,@sal,’Record Deleted’,getdate()
      print ‘Record Deleted from Master Table – instead of delete trigger raised’
      end
      end
      ————————
      -MALKEET SINGH

      Reply
  • Thank you Sir very much!

    Reply
  • dear sir,
    i want to insert, update,and delete from two table using triggers ONE TABLE MASTER TABLE,AND ONE TRANSACTION TABLE,IN MASTE TABLE A/CNO = ‘A001’,BALANCE = 20000,
    IN TRANSACTION TABLE A/C NO = ‘A001’,A/CTYPE = WITHDRAWL/DEPOSIT,AMOUNT= 200

    I HAVE WITHDRAWL 200 FROM TRANSACTION TABLE AND IT SHOULD BE WOTHDRAWL FROM MASTER TABLE AND BE DELETED 200 FROM MASTER HOW CAN I DO IT IN TRIGGERS
    AND IF EXIST THE AC TYPE ……..

    HOW CAN IT POSSIBLE ?????

    Reply
  • You are the GURU of SQL,
    Great work…..

    Thanks

    Reply
  • How to fire insert trigger foreach row

    when using
    insert into ()
    select from

    Reply
  • i want to create trigger if i update one column value. it should update another Table column’s value in same DB

    Reply
  • Dear Pinal sir,

    Its a great SQL Tip for me.

    Thanks & Regards,
    Nikhildas

    Reply
  • Vishnu

    Dave Can u please explain Trigger

    Reply
  • while running trigger its other related transaction will be locked?
    I have trigger for one column update from Stored procedyre. when updated its ruunning. my doub is, if trigger takes more time means . whether other remaining query will be locked?

    Please clarify … pls help me

    Reply
  • Hi Pinal Dave,

    When we create “Instead of delete” trigger on particular table , after creating of cursor want to delete rows from table and in trigger some condition exists.
    if given condition is satisfied by compiler then cursor rollback deleted records to table and condition is not satisfied then cursor commit that record.
    But Here I have question that “Instead of delete ” cursor set automatically “begin transaction” while cursor invoked or we have to set “begin transaction” before invoking the trigger action?

    Thanks in advance…
    and awaiting for your earliest reply…..

    Reply

Leave a Reply