April 22, 2013 at 11:23 pm
Comments posted to this topic are about the item Nested triggers 1
Tom
April 22, 2013 at 11:29 pm
Very good question. Till all the QOD, this question take more time. You need to look and dry run every t-sql code carefully. Finally i am happy, i got point 🙂
:Whistling:
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
April 23, 2013 at 12:03 am
gr8888 question Tom 🙂
Thanks a lot!!
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 23, 2013 at 2:22 am
Bad question! Too much stuff, no value!
It's take too long to check all the script!
April 23, 2013 at 2:46 am
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).
April 23, 2013 at 2:58 am
What is the difference between the answer 3 and 6?
5, 3, 1
April 23, 2013 at 3:37 am
I'd go with what Hugo said. Also, it's my preference if the question can use some more memorable field names just to try and help make sense of what is going on - even if it is just name or whatever. All the a, b, c, x, y stuff makes it hard for me to trace through what is going on mentally - maybe that's just me of course.
Thanks for the idea and demonstration thereof anyhow.
April 23, 2013 at 5:22 am
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).
Tom
April 23, 2013 at 6:41 am
Very very interesting one, thank you Tom for posting.(so simple and yet so complex)
I have one doubt - as the "nested" is out of the scope now it is just a "instead of" triggers that cascade the actions (like Hugo has pointed) - I am not sure if this possible as I have never came across or never tried (and not even sure where to start), is it possible that say 3 triggers and make them execute it sequentially? (like trigger1... and then when complete let it execute trigger2 and then trigger3...)?
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
April 23, 2013 at 6:49 am
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."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).
+1 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 23, 2013 at 7:40 am
This was removed by the editor as SPAM
April 23, 2013 at 8:09 am
Hugo Kornelis (4/23/2013)
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).
+1
HaHa..
Tom.. I took it as a personal challenge to work out your entire question in my brain and you should see the pride when I got the answer correct only to realize after seeing the description, I didn't pay even the slightest of attention to the actual question in your question....
I did enjoy working through your Qotd though.. Thanks for it..
___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:
April 23, 2013 at 9:01 am
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
April 23, 2013 at 9:54 am
Thanks Tom.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 23, 2013 at 11:11 am
It took a while... Thanks, Tom!
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply