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 Thursday, March 14, 2013 2:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
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
Post #1431267
Posted Friday, March 15, 2013 9:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 8:24 AM
Points: 11, Visits: 44
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...
Post #1431858
Posted Friday, March 15, 2013 10:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
brettstahlman (3/15/2013)
I agree that the "instead of delete" trigger will not be fired, [b]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
Post #1431860
Posted Saturday, March 16, 2013 7:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 8:24 AM
Points: 11, Visits: 44
opc.three (3/15/2013)
brettstahlman (3/15/2013)
I agree that the "instead of delete" trigger will not be fired, [b]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.





Post #1431892
Posted Saturday, March 16, 2013 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
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
Post #1431903
Posted Saturday, March 16, 2013 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 8:24 AM
Points: 11, Visits: 44
opc.three (3/16/2013)
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.


I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?
Post #1431905
Posted Saturday, March 16, 2013 11:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 20,745, Visits: 32,561
brettstahlman (3/16/2013)
opc.three (3/16/2013)
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.


I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?


No.

A good example of changes made by MS that comes to my mind is the WebTasks procedures. Fully documented and useable without special permissions. Then one day MS decides to change that behaviour in a SP and code that worked for many people suddenly broke. IIRC, this change wasn't even communicated prior to it being implemented. What does this have to do with this discussion? Everything.

This is why you do regression testing when applying CU's, Service Packs, upgrades; to make sure that the behaviour of your code hasn't changed unexpectedly and with possible disastrous effects to your data. From what I have been reading, you are expecting 100% perfection in the MS documentation. Good luck with that.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431921
Posted Saturday, March 16, 2013 11:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 8:24 AM
Points: 11, Visits: 44
Lynn Pettis (3/16/2013)
brettstahlman (3/16/2013)
opc.three (3/16/2013)
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.


I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?


No.

A good example of changes made by MS that comes to my mind is the WebTasks procedures. Fully documented and useable without special permissions. Then one day MS decides to change that behaviour in a SP and code that worked for many people suddenly broke. IIRC, this change wasn't even communicated prior to it being implemented. What does this have to do with this discussion? Everything.

This is why you do regression testing when applying CU's, Service Packs, upgrades; to make sure that the behaviour of your code hasn't changed unexpectedly and with possible disastrous effects to your data. From what I have been reading, you are expecting 100% perfection in the MS documentation. Good luck with that.


I'm not sure why you said "No": I asked whether the previous poster agreed that the behavior and documentation were inconsistent; far from contradicting my assertion, you provided another example of this sort of inconsistency, and even seemed to be implying that I should ignore the spec, relying upon rigorous testing instead. Even if the spec is so unreliable as to make this a reasonable approach, it should be pointed out that you haven't contradicted my basic assertion. Recall that I didn't say a developer shouldn't test his implementation. Testing is necessary, but not sufficient. Additionally, the implementation should not rely upon undocumented "features", even if they seem to work consistently. As a general rule (your WebTasks example notwithstanding), such features are more likely to change in a non-backwards-compatible way than spec-conformant behavior...
Post #1431927
Posted Saturday, March 16, 2013 11:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
I agree in so far as I could not find anything in spec related to this behavior, but not that it signifies any type of bug. I do not have a problem with it since the behavior is consistent and predictable on 2008 R2 and 2012, the versions I tried my code samples in my previous posts on.

The behavior kind of makes sense when you consider of the number of permutations implied by the various ways it could have been implemented. If recursive triggers is off, and after triggers can fire instead triggers (your original concern was that they do not), and in turn instead triggers can fire after triggers (currently they do, even with recursive triggers off, which makes sense) then in your scenario predictability could become an issue as we would have to provide special code in our after triggers to determine which scenario we were in:

1. the scenario where an after trigger fired first, then the instead trigger, then the after trigger a second time
2. the instead trigger fired first, then the after trigger for the first time

For me the lack of documentation does not point to a bug, it points to a lack of documentation.

PS Lynn and I were typing at the same time so I figured I would add this upon reading his post. While the SQL Server documentation is amongst the best in terms of RDBMS platforms I have worked with, it is clear that both of us are acutely aware of some of its shortcomings, of which you are highlighting one. SQL Server is a vast product and sometimes the nuances of a feature are not well addressed, or explicitly addressed at all. If you are concerned with this particular shortcoming I would encourage you to open a ticket on http://connect.microsoft.com and link back to this thread. I think we have covered the topic quite well.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1431929
Posted Saturday, March 16, 2013 5:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 20,745, Visits: 32,561
brettstahlman (3/16/2013)
Lynn Pettis (3/16/2013)
brettstahlman (3/16/2013)
opc.three (3/16/2013)
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.


I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?


No.

A good example of changes made by MS that comes to my mind is the WebTasks procedures. Fully documented and useable without special permissions. Then one day MS decides to change that behaviour in a SP and code that worked for many people suddenly broke. IIRC, this change wasn't even communicated prior to it being implemented. What does this have to do with this discussion? Everything.

This is why you do regression testing when applying CU's, Service Packs, upgrades; to make sure that the behaviour of your code hasn't changed unexpectedly and with possible disastrous effects to your data. From what I have been reading, you are expecting 100% perfection in the MS documentation. Good luck with that.


I'm not sure why you said "No": I asked whether the previous poster agreed that the behavior and documentation were inconsistent; far from contradicting my assertion, you provided another example of this sort of inconsistency, and even seemed to be implying that I should ignore the spec, relying upon rigorous testing instead. Even if the spec is so unreliable as to make this a reasonable approach, it should be pointed out that you haven't contradicted my basic assertion. Recall that I didn't say a developer shouldn't test his implementation. Testing is necessary, but not sufficient. Additionally, the implementation should not rely upon undocumented "features", even if they seem to work consistently. As a general rule (your WebTasks example notwithstanding), such features are more likely to change in a non-backwards-compatible way than spec-conformant behavior...


The "No" may have been a misinterpretation of what I had read. With that, I apologize.

What I spoke of was not an inconsistency, it was a significant change in behaviour of a well documented capability at the time. It was changed without any notice.

What I am saying here is that you need to perform full regression testing on your application when applying CU's, service packs, or upgrades in you development and QA environments prior to applying them in production. This is needed to ensure that demonstrated behaviour (that which you currently expect and have seen) doesn't change and cause problems. If it does change, then you need to determine what changes you need to do to make to correct the problem.

I would say you need to do this even if the documentation was 100% accurate now. There is nothing to say MS might not making changes that could change behaviour in ways you may not anticipate, and do so without much fanfare (if any at all).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431948
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse