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»»»

Nested triggers 1 Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 11:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:47 AM
Points: 7,855, Visits: 9,603
Comments posted to this topic are about the item Nested triggers 1

Tom
Post #1445274
Posted Monday, April 22, 2013 11:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
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





Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1445275
Posted Tuesday, April 23, 2013 12:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 1,942, Visits: 2,381
gr8888 question Tom
Thanks a lot!!



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1445278
Posted Tuesday, April 23, 2013 2:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 2,591, Visits: 2,445
Bad question! Too much stuff, no value!
It's take too long to check all the script!
Post #1445311
Posted Tuesday, April 23, 2013 2:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #1445316
Posted Tuesday, April 23, 2013 2:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 2,591, Visits: 2,445
What is the difference between the answer 3 and 6?
5, 3, 1
Post #1445319
Posted Tuesday, April 23, 2013 3:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:37 AM
Points: 1,776, Visits: 1,170
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.
Post #1445329
Posted Tuesday, April 23, 2013 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:47 AM
Points: 7,855, Visits: 9,603
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
Post #1445363
Posted Tuesday, April 23, 2013 6:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1,030, Visits: 2,338
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.
Post #1445390
Posted Tuesday, April 23, 2013 6:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 1,942, Visits: 2,381
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/
Post #1445394
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse