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."
The question IDEA is very good. The combination of instead of triggers and the nested trigger option is very likely to fool people.
But the execution is poor. Way to much code to decipher. I am prepared to spend 2 or 3 minutes on a QotD, not 15 or more.
For a good QotD, you have to think about what you want to test. Try to focus on one thing only, or maybe two small things that can easily be combined. Than try to find a way to phrase the question that adds as little as possible.
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).
None of this is required to test the actual subject of the question. You could have done it with no code at all: "What is the effect of the nested trigger option?" and then have some distracters suggesting that it is deprecated and does nothing, or that described the function of the recursive triggers option.
And if you really want to do it with code - use one table, one view with a simple select everything from table, a delete trigger on the table that deletes from the view (based on matching primary key in the deleted table), and a delete trigger on the view that deletes from the table (based on the same mathcing primary key). Add a row to the table, delete it again, then ask how many rows there will be in the table.
Writers of prose know that the hardest part of their job is to trim down their first version. Ruthlessly cut out all those beautiful paragraphs they spent hours writing, becuase, in the end, they are not really necessary. The same applies to QotD authors. "Kill your darlings".
(My own QotD's probably suffer from the same problem -though I don't think I've ever had *this* amount of code-. It's always easier to kill someone else's darlings).
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis