Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Why is "instead of delete" trigger not fired by delete inside "after update" trigger Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 9:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 8:24 AM
Points: 11, Visits: 44
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.
Post #1430725
Posted Thursday, March 14, 2013 12:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:20 AM
Points: 447, Visits: 262
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
Post #1430771
Posted Thursday, March 14, 2013 12:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1430780
Posted Thursday, March 14, 2013 5:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 8:24 AM
Points: 11, Visits: 44
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.
Post #1430893
Posted Thursday, March 14, 2013 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1431005
Posted Thursday, March 14, 2013 8:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 8:24 AM
Points: 11, Visits: 44
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.
Post #1431017
Posted Thursday, March 14, 2013 10:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1431105
Posted Thursday, March 14, 2013 10:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 8:24 AM
Points: 11, Visits: 44

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.
Post #1431129
Posted Thursday, March 14, 2013 1:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1431225
Posted Thursday, March 14, 2013 2:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 8:24 AM
Points: 11, Visits: 44
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...
Post #1431263
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse