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


DDL Trigger for Drop_Table


DDL Trigger for Drop_Table

Author
Message
monica.parsi
monica.parsi
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 153
Comments posted to this topic are about the item DDL Trigger for Drop_Table
Tom Thomson
Tom Thomson
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43385 Visits: 12985
If you use that code to try to create the trigger, the trigger will do nothing - the code is syntactically incorrect because the create trigger statement is not the first statement in the batch, so the trigger is not even created. So the correct answer is that trigger does nothing and the table is dropped.

I think you should fix that syntax error in teh question, Steve. Hehe

Tom

Raghavendra Mudugal
Raghavendra Mudugal
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4956 Visits: 2958
TomThomson (5/4/2014)
If you use that code to try to create the trigger, the trigger will do nothing - the code is syntactically incorrect because the create trigger statement is not the first statement in the batch, so the trigger is not even created. So the correct answer is that trigger does nothing and the table is dropped.

I think you should fix that syntax error in teh question, Steve. Hehe



+1

.. well go and fix it with "Go"... nice. :-)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Raghavendra Mudugal
Raghavendra Mudugal
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4956 Visits: 2958
Thank you for the post, good one.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
free_mascot
free_mascot
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13237 Visits: 2250
Interesting one. Thanks.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
domenico.delbrocco
domenico.delbrocco
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 91
I was afraid the question could have been related to CDC , but reasoning on transactions and triggers I guessed the correct answer

Thanks
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29576 Visits: 12830
Great question, Mounika. Thanks!

The design choice to make DDL triggers fire after the event has many consequences. This question points out one (and one that can be very easy to overlook; I am actually surprised that a total of 32% (at this time) of the answer is correct).

But there are other consequences as well. Some people have tried to use DDL triggers to prevent people who otherwise do need DDL permissions from making specific changes that can affect performance (big allocations or deallocations, schema stability locks that are kept for a long time, etc). It didn't work. The triggers do not actually prevent these changes, they allow them to happen, then check and if the triggers thinks the change should not have been made, it will be rolled back - costing even more resources!

I will not hold my breath for it - but I think that it would be great if Microsoft could give us an option to make DDL trigers fire *before* the change.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29576 Visits: 12830
TomThomson (5/4/2014)
If you use that code to try to create the trigger, the trigger will do nothing - the code is syntactically incorrect because the create trigger statement is not the first statement in the batch, so the trigger is not even created. So the correct answer is that trigger does nothing and the table is dropped.

True. But the question was not related to whether the code succeeds. the question doesn't say "we shall run the below code", but "we shall create a trigger" and "the code is as follows". If I hand exactly that assignment with this code to a junior DBA, I expect him to run the code, see the error, make the obvious fix and run it again, because I asked him to create the trigger, not to act like a robot.

If there had been an error in the code that could not be fixed unambiguously, I would agree with you. But in this case the error and the fix are so obvious that they don't make it impossible, or even harder, to answer the actual question.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Tom Thomson
Tom Thomson
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43385 Visits: 12985
Hugo Kornelis (5/5/2014)
TomThomson (5/4/2014)
If you use that code to try to create the trigger, the trigger will do nothing - the code is syntactically incorrect because the create trigger statement is not the first statement in the batch, so the trigger is not even created. So the correct answer is that trigger does nothing and the table is dropped.

True. But the question was not related to whether the code succeeds. the question doesn't say "we shall run the below code", but "we shall create a trigger" and "the code is as follows". If I hand exactly that assignment with this code to a junior DBA, I expect him to run the code, see the error, make the obvious fix and run it again, because I asked him to create the trigger, not to act like a robot.

If there had been an error in the code that could not be fixed unambiguously, I would agree with you. But in this case the error and the fix are so obvious that they don't make it impossible, or even harder, to answer the actual question.

Oh, I agree with you. I don't think anyone who knows how the trigger works would choose the wrong answer because of this error. I just think it would be a good idea to insert the missing GO so that the syntax is correct. Awarding back points, as sometimes happens with question errors, would be a silly over-reaction for this one. I thought the emoticon at the end of my post would indicate that I didn't regard it as serious.

Tom

sestell1
sestell1
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5307 Visits: 3609
Great question! Thanks!!
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