SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nested triggers 1


Nested triggers 1

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26437 Visits: 12506
Comments posted to this topic are about the item Nested triggers 1

Tom

Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2246 Visits: 1549
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 !!!
www.GrowWithSql.com
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5470 Visits: 2768
gr8888 question Tom :-)
Thanks a lot!!

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7638 Visits: 3401
Bad question! Too much stuff, no value!
It's take too long to check all the script!

I run on tuttopodismo
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19161 Visits: 12426
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
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7638 Visits: 3401
What is the difference between the answer 3 and 6?
5, 3, 1

I run on tuttopodismo
call.copse
call.copse
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5701 Visits: 2014
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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26437 Visits: 12506
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

Raghavendra Mudugal
Raghavendra Mudugal
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3400 Visits: 2958
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.
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5470 Visits: 2768
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search