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 Monday, March 18, 2013 2:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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


I suppose you and I just have very different notions of what constitutes a bug. Suffice it to say, most software developers would love to have a customer who takes your view of these things...

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.


Agreed. I appreciate the time you took to investigate and offer your perspective...
Post #1432330
Posted Monday, March 18, 2013 2:18 PM
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)
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).


That's a bit scary... Ah, well. I appreciate your time and perspective...
Post #1432333
Posted Monday, March 18, 2013 2:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 2,194, Visits: 3,304
brettstahlman (3/18/2013)
Lynn Pettis (3/16/2013)
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).


That's a bit scary... Ah, well. I appreciate your time and perspective...



But SQL Server is still vastly better on this point that Oracle. Every tiny subchange in Oracle, from say 11.n.n to 11.n.n+1 can cause huge query and performance issues. While SQL Server has things come up from time to time, it's nothing close to as awful as Oracle in this area.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1432336
Posted Monday, March 18, 2013 2:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
brettstahlman (3/18/2013)
opc.three (3/16/2013)
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.


I suppose you and I just have very different notions of what constitutes a bug. Suffice it to say, most software developers would love to have a customer who takes your view of these things...

Maybe so. I can agree to disagree here. I guess I cannot reasonably hold a vendor (or any software developer to your point) to that standard. I am not saying that customers do not try (this thread is a case-in-point ) but I cannot go where you're going with this particular one.

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.


Agreed. I appreciate the time you took to investigate and offer your perspective...

The pleasure was all mine. Thanks for the dialogue. Discussions like this are why I hang out here. If you open a Connect item please post the link.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1432352
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse