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 Tuesday, April 30, 2013 7:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 10, 2014 6:56 AM
Points: 1,176, Visits: 878
The idea for qotd is OK, but a script is too complicated to figure out the result within few minutes I'm willing to spend on it.
There are a lot of mistakes, typos and non standard syntax.
--***** tidy up part 1: drop the views and table created above *****
delete dbo.tmp1 --missing "v" -ok, a typo
go
delete dbo.vtmp
go
delete dbo.tmp1
--missing "go"

Delete statement deletes data, does not drop the table/view.

Standard syntax for delete statement is DELETE FROM Table. In transact-sql you can skip FROM keyword, but unless you plan to stick with mssql for the rest of your life, forget shortcuts and use standard syntax.

SQL code should be as simple as possible. Meaning: avoid the chain logic if possible (A triggers B, which triggers C or A, which then triggers D). If not possible, at least document the logic with embedded comments.

Hope you'll do better next time.
Post #1448035
Posted Tuesday, April 30, 2013 5:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 8,568, Visits: 9,073
Robert-378556 (4/30/2013)
The idea for qotd is OK, but a script is too complicated to figure out the result within few minutes I'm willing to spend on it.
See earlier post which points out why is is as complicated as it is. I agree it's too complicated. But I don't think it's too complicated to get the result in a few minutes, just that it's sufficiently complicated that it will take a few minutes of hard thinking (which is too complicated for QotD).

There are a lot of mistakes, typos and non standard syntax.
....

the "missing" go is redundant, so not missing.
Delete statement deletes data, does not drop the table/view.

Stupid error on my part. Obviously my brain wasn't working properly there. Or maybe my fingers. I should copy and paste from my test harness, instead of retyping - the views and table are gone from my sandpit, so I must have had drop view and drop table statements there.
Standard syntax for delete statement is DELETE FROM Table. In transact-sql you can skip FROM keyword, but unless you plan to stick with mssql for the rest of your life, forget shortcuts and use standard syntax.
I don't expect to use a different RDBMS ever again, unless I get around to sorting out the cross-statement spool issue so that I can write a strictly declarative toy RDBMS. And anyway, I acquired the habit of leaving out that redundant FROM long before MSSQL existed, so there must be [or at least have been] another RDBMS that allowed me to leave it out.
SQL code should be as simple as possible. Meaning: avoid the chain logic if possible (A triggers B, which triggers C or A, which then triggers D). If not possible, at least document the logic with embedded comments.
Trouble with embedded comments in a QotD is that there would be a serious risk that the answer will be in the comments. And the trouble with trying to avoid chain logic (trigger causes another trigger) is quite simply that without it is impossible to talk about nested triggers, so the topic stated for this question should have indicated that your comment on that topic was somehat pointless.
Hope you'll do better next time.
I'm afraid the next one is probably just as bad (apart from delete instead of drop, I hope: the system on SQLServerCentral doesn't allow me to see my question after it's accepted until it's published, so I can't be sure). After that I'll maybe revert to my old question style and submit things that test knowledge and not ability to execute code in one's head (instead of things that try to test both). Or maybe not - maybe it's not a bad thing for a QotD to require 5 minutes hard thinking?


Tom
Post #1448275
Posted Tuesday, May 7, 2013 2:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 3,537, Visits: 2,647
Gosh. This question should be appeared in "question of the week" rather than "question of the day". Too lengthy. Don't know if only i felt this little heat. But after spending some good 20 odd minutes, got this correct.
Post #1450019
Posted Tuesday, May 7, 2013 5:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 10, 2014 6:56 AM
Points: 1,176, Visits: 878
L' Eomot Inversé (4/30/2013)
After that I'll maybe revert to my old question style and submit things that test knowledge and not ability to execute code in one's head (instead of things that try to test both). Or maybe not - maybe it's not a bad thing for a QotD to require 5 minutes hard thinking?

Purpose of qotd is to have fun, test knowledge and learn something.
I'm very busy, so on a tilt I go to do something different (read news, qotd, walk,...), during any of these activities I do not appreciate requirement to do 5 minutes of hard thinking of the same kind I got the tilt from.
Post #1450066
Posted Friday, May 31, 2013 1:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:24 AM
Points: 1,144, Visits: 299
more than 20 lines in the questions ? remains two possible actions :
1) copy / code and runs in SSMS and then quietly report the answer to get 1 point
2) randomly try one of the choice

I definitly don't want to use the first choice and had a bad guess

luckyless !
Post #1458541
Posted Thursday, June 12, 2014 6:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:11 AM
Points: 2,382, Visits: 177
Another good Trigger question.
Post #1580022
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse