none
Can an Insert Trigger Safely Insert Another Row? RRS feed

  • Question

  • Hi,

    I want to write an insert trigger that Inserts a second row when the inserted row meets certain criteria. For instance, if a row is inserted with a value between 10 and 20, I want my trigger to insert another row. My concern is whether this could cause an infinite recursive loop. Will an insert inside of an insert trigger trigger itself? I would think it wouldn't but I want to make sure before trying it. Thanks!

    Thursday, November 11, 2010 10:27 PM

Answers

  • If you are inserting another row into the same table, there is a database option that controls whether the insert done inside the trigger will fire the trigger.  By default, that option is False, that is, the trigger will not fire again.  To see what the option is set to in your database, run

    Select DATABASEPROPERTYEX('AdventureWorks', 'IsRecursiveTriggersEnabled')

    (Of course, replace 'AdventureWorks' with your database name).

    If it returns 0, then the insert into the table in the trigger will not cause the trigger to be fired again.

    Note that the above discussion only applies to a trigger inserting into the same table.  If the trigger inserts rows into another table and that second table also has an insert trigger, then there is a system option (Nested Triggers) that determines whether the trigger for the second trigger will fire.

    By default Recursive Triggers is FALSE, but the default for Nested Triggers is TRUE.

    Tom

    • Marked as answer by _xr280xr_ Friday, November 12, 2010 3:45 PM
    Thursday, November 11, 2010 10:43 PM
  • From what you describe you will have a recursive trigger situation. You are right that your insert will cause another triggering of the trigger so you need to design it with this in mind. i.e. if you are inserting again where between 10 and 20 is the new insert outside of this range? if so on the second run it will check and not repeat the insert. So you should design your trigger code to deal with it. You can disable recursion at the database level but again this will affect other processes. Have a look at the following for more info...

    http://msdn.microsoft.com/en-us/library/aa214644(SQL.80).aspx

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by _xr280xr_ Friday, November 12, 2010 3:45 PM
    Thursday, November 11, 2010 10:49 PM

All replies

  • If you are inserting another row into the same table, there is a database option that controls whether the insert done inside the trigger will fire the trigger.  By default, that option is False, that is, the trigger will not fire again.  To see what the option is set to in your database, run

    Select DATABASEPROPERTYEX('AdventureWorks', 'IsRecursiveTriggersEnabled')

    (Of course, replace 'AdventureWorks' with your database name).

    If it returns 0, then the insert into the table in the trigger will not cause the trigger to be fired again.

    Note that the above discussion only applies to a trigger inserting into the same table.  If the trigger inserts rows into another table and that second table also has an insert trigger, then there is a system option (Nested Triggers) that determines whether the trigger for the second trigger will fire.

    By default Recursive Triggers is FALSE, but the default for Nested Triggers is TRUE.

    Tom

    • Marked as answer by _xr280xr_ Friday, November 12, 2010 3:45 PM
    Thursday, November 11, 2010 10:43 PM
  • From what you describe you will have a recursive trigger situation. You are right that your insert will cause another triggering of the trigger so you need to design it with this in mind. i.e. if you are inserting again where between 10 and 20 is the new insert outside of this range? if so on the second run it will check and not repeat the insert. So you should design your trigger code to deal with it. You can disable recursion at the database level but again this will affect other processes. Have a look at the following for more info...

    http://msdn.microsoft.com/en-us/library/aa214644(SQL.80).aspx

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by _xr280xr_ Friday, November 12, 2010 3:45 PM
    Thursday, November 11, 2010 10:49 PM
  • Thanks!
    Friday, November 12, 2010 3:44 PM