March 13, 2013 at 9:40 pm
One of my tables has both an "after update" and an "instead of delete" trigger. When I perform a delete within the after update trigger, the instead of delete trigger is not fired. Although I don't actually want the instead of delete trigger to fire in this particular case, I'm troubled by the fact that I haven't seen anything in the documentation that indicates it shouldn't. If I understand correctly, the recursion is indirect, and hence, should not be prevented by RECURSIVE_TRIGGERS disabled (the setting in my database).
The only thing I found in the docs that seemed close to explaining what I'm seeing is the following from the SQL Server 2008 R2 documentation:
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. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.
This statement, however, applies only to the case in which the statement triggering the recursive call to the instead of delete trigger is itself within the instead of delete trigger. In my case, the delete statement in question is in an after update trigger, so the statement from the docs seems not to apply.
I noticed that at least one other user has been puzzled by this behavior:
http://www.sqlservercentral.com/Forums/Topic1387252-23-1.aspx
Still no response to his post...
Does anyone know what's going on here? I could explicitly disable triggers before executing the delete statement within the after update trigger, but I don't want to do this unnecessarily...
Thanks,
Brett S.
March 14, 2013 at 12:17 am
Hi Brett,
as I understand your case is about nested triggers.
Check the article:
http://msdn.microsoft.com/en-us//library/ms190739.aspx
"Nested Triggers
Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). "
Nested triggers are configured on the server side:
select * from sys.configurations where name like 'nested triggers'
If this value is set to 0, that means that nested triggers are disabled.
To turn it off you can use next script:
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO
March 14, 2013 at 12:38 am
brettstahlman (3/13/2013)
If I understand correctly, the recursion is indirect, and hence, should not be prevented by RECURSIVE_TRIGGERS disabled (the setting in my database).
I do not think it is considered indirect. I have no documentation to point to, only evidence. If you enable recursive triggers and run your test you'll quickly see an error like:
Msg 217, Level 16, State 1, Procedure YourAfterTriggerName, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
USE [master]
GO
-- try it with it ON and you'll see a recursive limit error
--ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO
USE [YourDatabaseName]
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.maintable')
AND type IN ( N'U' ) )
DROP TABLE dbo.maintable;
GO
CREATE TABLE dbo.maintable ( triggername SYSNAME );
GO
CREATE TRIGGER dbo.instead_insert_maintable ON dbo.maintable
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO maintable
VALUES ( OBJECT_NAME(@@PROCID) );
END
GO
CREATE TRIGGER dbo.after_insert_maintable ON dbo.maintable
AFTER INSERT
AS
BEGIN
INSERT INTO maintable
VALUES ( OBJECT_NAME(@@PROCID) );
END
GO
INSERT INTO dbo.maintable
( triggername )
VALUES ( N'test' )
GO
SELECT *
FROM dbo.maintable
go
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 5:35 am
The case you have constructed does indeed involve direct recursion (as defined by the docs) and is fundamentally different from mine: you're executing an insert statement from within an instead of insert trigger; I'm executing a delete from within an after update. In my case, there would be direct recursion only if the instead of delete trigger performed an update on the table that was the target of the original update.
March 14, 2013 at 8:23 am
I see what you mean. It appears when the AFTER trigger is the first one to fire, nothing carried out within that trigger done against the base table will fire an INSTEAD trigger on that base table. I tried different settings of nested and recursive triggers and could not get the INSTEAD trigger to fire and like you could not find anything in the docs explaining the behavior.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 8:40 am
Thanks. Hopefully someone from the SQL Server team will weigh in on this, as it appears there may be an error in the documentation/spec. I started to call it an ambiguity, but the documentation states unambiguously that RECURSIVE_TRIGGERS can't be used to prevent indirect recursion, and the case I've described qualifies as indirect recursion, according to the definition provided in the documentation.
March 14, 2013 at 10:34 am
brettstahlman (3/14/2013)
Thanks. Hopefully someone from the SQL Server team will weigh in on this, as it appears there may be an error in the documentation/spec. I started to call it an ambiguity, but the documentation states unambiguously that RECURSIVE_TRIGGERS can't be used to prevent indirect recursion, and the case I've described qualifies as indirect recursion, according to the definition provided in the documentation.
I am not convinced your use case qualifies as indirect recursion since the action in the AFTER trigger does not cause the INSTEAD trigger to fire. If it did, and that inturn caused the AFTER trigger to fire again I still do not think it would qualify an indirect. Here is why.
From http://msdn.microsoft.com/en-us/library/ms190739(v=sql.105).aspx:
Indirect recursion
This recursion occurs when a trigger fires and performs an action that causes another trigger of the same type (AFTER or INSTEAD OF) to fire. This second trigger performs an action that causes the original trigger to fire again. In other words, indirect recursion can occur when an INSTEAD OF trigger is called for a second time, but not until another INSTEAD OF trigger is called in between. Likewise, indirect recursion can occur when an AFTER trigger is called for a second time, but not until another AFTER trigger is called in between. For example, an application updates table T1. This update causes AFTER trigger Trig1 to fire. Trig1 updates table T2, and this update causes AFTER trigger Trig2 to fire. Trig2 in turn updates table T1 that causes AFTER trigger Trig1 to fire again.
Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0.
I think this is a matter of entry point. It seems that when the entry point to the chain of triggers firing is an INSTEAD trigger direct recursion is in play. But if the first trigger to fire is an AFTER trigger then it cannot initiate direct recursion when the next trigger in the chain would be an INSTEAD trigger. I agree this is an odd distinction in the behavior and I cannot find any documentation to explain the effect.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 10:59 am
I am not convinced your use case qualifies as indirect recursion since the action in the AFTER trigger does not cause the INSTEAD trigger to fire. If it did, and that inturn caused the AFTER trigger to fire again I still do not think it would qualify an indirect. Here is why.
Actually, the case I described seems not to involve *any* recursion, according to the definition in the docs, since "sameness" in the context of recursion refers to triggers - not tables - and there's no reason either of the triggers in question would trigger more than once.
March 14, 2013 at 1:34 pm
That seems to be the ticket. The instead trigger is fired once, and the after trigger is what continues to recurse.
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
GO
USE [YourDatabaseName]
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.maintable')
AND type IN ( N'U' ) )
DROP TABLE dbo.maintable;
GO
CREATE TABLE dbo.maintable ( triggername SYSNAME );
GO
CREATE TRIGGER dbo.instead_insert_maintable ON dbo.maintable
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO maintable
VALUES ( OBJECT_NAME(@@PROCID) );
END
GO
CREATE TRIGGER dbo.after_insert_maintable ON dbo.maintable
AFTER INSERT
AS
BEGIN
IF @@nestlevel < 20
INSERT INTO maintable
VALUES ( OBJECT_NAME(@@PROCID) );
END
GO
INSERT INTO dbo.maintable
( triggername )
VALUES ( N'test' )
GO
SELECT *
FROM dbo.maintable
go
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 2:23 pm
Still, the case you've constructed is fundamentally different, as it involves an insert within an insert, which is inherently recursive. Mine involves a delete within an after insert, which would appear to be completely non-recursive. "Nested" perhaps, but not recursive...
March 14, 2013 at 2:41 pm
What the code sample illustrates is that an AFTER trigger firing for an event listened for by an INSTEAD trigger on the same table will not fire that INSTEAD trigger, regardless of the recursion or nested triggers settings, which was the root of your concern: an AFTER UPDATE trigger that issues a delete against the base table does not fire the INSTEAD DELETE trigger on that table.
USE [master]
GO
-- comment the cases as needed
--case 1
ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
GO
EXEC sys.sp_configure
@configname = 'nested triggers',
@configvalue = 1;
RECONFIGURE;
GO
--case 2
ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
GO
EXEC sys.sp_configure
@configname = 'nested triggers',
@configvalue = 0;
RECONFIGURE;
GO
--case 3
ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO
EXEC sys.sp_configure
@configname = 'nested triggers',
@configvalue = 0;
RECONFIGURE;
GO
--case 4
ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO
EXEC sys.sp_configure
@configname = 'nested triggers',
@configvalue = 1;
RECONFIGURE;
GO
USE [YourDatabaseName]
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.maintable')
AND type IN ( N'U' ) )
DROP TABLE dbo.maintable;
GO
CREATE TABLE dbo.maintable ( triggername SYSNAME );
GO
INSERT INTO dbo.maintable
( triggername )
VALUES ( N'test' )
GO
CREATE TRIGGER dbo.instead_delete_maintable ON dbo.maintable
INSTEAD OF DELETE
AS
BEGIN
INSERT INTO maintable
VALUES ( OBJECT_NAME(@@PROCID) );
END
GO
CREATE TRIGGER dbo.after_update_maintable ON dbo.maintable
AFTER UPDATE
AS
BEGIN
INSERT INTO maintable
VALUES ( OBJECT_NAME(@@PROCID) );
END
GO
UPDATE dbo.maintable
SET triggername = 'updated'
WHERE triggername = 'test';
GO
SELECT *
FROM dbo.maintable
go
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 15, 2013 at 9:25 pm
I agree that the "instead of delete" trigger will not be fired, but because the docs seem to indicate that it should be, 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...
March 15, 2013 at 10:02 pm
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?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2013 at 7:27 am
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.
March 16, 2013 at 9:14 am
The reason why I disagree is because of the behavior of the after trigger when it is the instead trigger that is fired first. The instead trigger fires the after trigger, but even if the after trigger does something that should fire the original instead trigger on the same table, it does not fire regardless of the values of the nested or recursive settings.
In your case the after trigger fires first, but similar to the case I just described it will not fire an instead trigger on the same table regardless of the values of the nested or recursive settings.
Maybe the documentation has a hole in it, it certainly would not be the first time, but the behavior seems to be predictable and consistent. Just my two cents.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply