L' Eomot Inversé (4/23/2013)
Hugo Kornelis (4/23/2013)
The first sentence of the explanation should probably have read "Instead of triggers can cascade regardless of the setting of the nested trigger server option."yes, you are write. Silly of me.
Hugo Kornelis (4/23/2013)
The code in this question contains:* A somewhat complex view definition that is not immediately obvious;
* Two instead of triggers that both are, while not really complex, not immediately obvious;
* A very complicated piece of code to populate the table (really, just putting in a VALUES clause with the five rows would have been so much easier).
*this* amount of code-. It's always easier to kill someone else's darlings).
There certainly was some unneccessary complexity. But that was intentional. You may recall that a few weeks back I responded to something (I can't remember what) in the forums with the comment that it was tempting me to produce a nasty complex question using instead of triggers. I resisted the temptation for a few days, but then succumbed and this was part of the result. Actually I reduced the complexity a lot from the first version, but it's still rather complex for a QotD.
I don't think the method of populating the table is unreasonable though - but maybe it is with only 5 rows (the original had varchar(27) instead of varchar(6) and 27 rows - using @ to Z instead of 5 using A to E).
The main quibble I have with the question was that figuring out the contents the dbo.tmp1 table would have been unreasonably time-consuming had I not just created the objects and executed a SELECT * FROM dbo.tmp1.
I couldn't figure out what the triggers would do until I knew what data was in the table, and trying to work it out from the formula would have taken longer than it took me to (a) figure out that the question hinged on the (non)effect on INSTEAD OF triggers of turning off nested triggers and (b) trace the execution of the DELETEs through the firing of the triggers to determine the final result.
Taking that long to puzzle out the sample data, and running the risk of a wrong answer if I made a mistake in doing so, seemed unreasonable to me when the generation of the sample data has nothing to do with the subject of the question. A simple INSERT INTO . . . VALUES construct would have put the data right there for me to see, and from there, I could have worked out the answer without running any code.
Tom, had you included 27 rows in the sample data instead of 5, you would probably have a mob of torch- and pitchfork-wielding SSC members at your door this morning!
The question does a great job of highlighting the fact that turning off nested triggers only affects AFTER triggers - that's the kind of esoteric SQL Server knowledge that I like to learn from a QotD!
Jason Wolfkill