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