Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nested triggers 1


Nested triggers 1

Author
Message
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1568 Visits: 1007
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.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
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

sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 Visits: 2774
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. :-)
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1568 Visits: 1007
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. :-D
jfgoude
jfgoude
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 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 !
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