Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Andy Warren Bypassing Triggers RE: Bypassing Triggers

  • November 24, 2003 at 4:47 pm

    #483469

    quote:

    If we execute the following statement, what trigger(s) will fire and how many times will they fire? Assume that the syntax is valid.

    insert into testtable (rowid, somevalue) select top 10 rowid, somevalue from someothertable

    Post your answer in the attached discussion forum. The best/most thorough answer gets a free copy of 'The Best of SQLServerCentral.com 2002'! Contest ends 1 week after this article is published.

    This doesn't give any background to the structure already in place on this table/database, nor the version of SQL Server in use. Did I miss something or is this just a rather open-ended question?

    For SQL Server 2000 you can have multiple triggers set to fire on an INSERT operation. Every one of these triggers would fire on testtable when this statement is executed. If there is an INSTEAD OF INSERT trigger configured on testtable then only that trigger would fire.

    NESTED and RECURSIVE TRIGGERS options will also affect what triggers will fire. If any of the initial triggers affect testtable with a new operation (INSERT, UPDATE, DELETE) then that will cause more triggers to fire if NESTED TRIGGERS is turned on. If the initial trigger(s) affect another table with an I/U/D operation then any triggers in place on that table will fire. This is a rather complex scenario if there are multiple triggers. RECURSIVE TRIGGERS is really only an effective setting in those instances where an operation on a table only has one trigger set to fire. If there is more than one trigger set to fire on an operation, then they can call each other in circular reference (not recursive) and it will depend on the NESTED TRIGGERS option and the configured nesting level for the database.

    If the NESTED TRIGGERS server-wide setting is set to off then no other triggers on this table will fire if the initial triggers affect testtable again, but triggers on other tables will fire (with the same stoppage if they affect testtable in return, or their own table).

    Edited by - slider on 11/24/2003 4:47:29 PM

    Edited by - slider on 11/24/2003 4:48:04 PM