Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Costly update trigger -70,000,000 logical reads for 30,000 rows updated! RE: Costly update trigger -70,000,000 logical reads for 30,000 rows updated!

  • February 2, 2009 at 10:59 am

    #936596

    GilaMonster (2/2/2009)

    Nested triggers means that an update to a table in a trigger can fire triggers on that table, providing it's not the same table. So, say you have Table1 that has an update trigger that inserts into table2. Table2 has an insert trigger that nserts into table3. If nested triggers is on and you update table1, then the trigger on table1 will insert into table2, will fire that trigger and will insert into tbl3. If it's off, the insert trigger on table2 won't fire.

    Recursive trigger controls if a trigger may fire itself due to data changes. If it's off, your trigger will not be called due to the updates that it does.

    Thanks, so that means I'm OK from that aspect, ie. this trigger will not fire itself repeatedly, since my db setting is off.

    That cleared it up.

    From http://msdn.microsoft.com/en-us/library/ms190739(SQL.90).aspx

    An AFTER trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]