opc.three (3/15/2013)
brettstahlman (3/15/2013)
I agree that the "instead of delete" trigger will not be fired, but because the docs seem to indicate that it should be[/u], I don't feel comfortable relying on the behaviour. If it turns out to be an "accident" of the current SQL Server implementation, there's no guarantee that code relying on this behaviour will continue to work in subsequent releases...Hmmm, can you point out those docs again please, the ones that indicate the "instead delete" trigger should fire when deleting data from the base table within an "after update" trigger, and the "after update" trigger was the first trigger to be fired on the base table?
See the definitions of nested triggers and recursive triggers below. Note in particular that according to the definition of recursive triggers, I have none - only nested triggers, which are enabled on my server. If you're not convinced that there is no recursion in my case, look carefully at the definition: in particular, note that the recursion always involves a single trigger: in the example, T1. In my case, there are 2 different triggers, and neither is called more than once.
Under "DML Triggers" in BOL, there's this:
If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively.
But note that it refers explicitly to an instead of trigger within an instead of trigger, and hence, does not apply in my case.
The bottom line is, I have a non-recursive, nested trigger, which isn't firing, even though the nesting level is only 2 (limit 32), and there's nothing in the documentation that indicates it shouldn't. Unless you can point to some sort of exception in BOL, which is applicable to my use case, I can only conclude that the docs are wrong...
BOL on Nested Triggers
Use the nested triggers option to control whether an AFTER trigger can cascade; that is, perform an action that initiates another trigger, which initiates another trigger, and so on. When nested triggers is set to 0, AFTER triggers cannot cascade. When nested triggers is set to 1 (the default), AFTER triggers can cascade to as many as 32 levels. INSTEAD OF triggers can be nested regardless of the setting of this option.
BOL on Recursive Triggers
SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.
Recursive triggers enable the following types of recursion to occur:
Indirect recursion
With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.
Direct recursion
With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.