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 (http://blog.SQLAuthority.com)

About these ads

26 thoughts on “SQL SERVER – 2005 Understanding Trigger Recursion and Nesting with examples

  1. 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!!!!!!!! ;)

  2. 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.

    • 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.

  3. 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.

  4. 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?

  5. 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

  6. 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.

  7. 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.

    • /*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

  8. 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 ?????

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

  10. 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

  11. 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…..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s