DDL Trigger for Drop_Table

Question

DDL Trigger for Drop_Table

monica.parsi

SSC Enthusiast

Points: 167
More actions
May 3, 2014 at 2:18 pm

#290961

Comments posted to this topic are about the item DDL Trigger for Drop_Table

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

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 SSChampion Points: 10658 More actions · Answer 2

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 SSChampion Points: 10658 More actions · Answer 3

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 One Orange Chip Points: 27169 More actions · Answer 4

Interesting one. Thanks.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

domenico.delbrocco SSChasing Mays Points: 617 More actions · Answer 5

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 SSC Guru Points: 64790 More actions · Answer 6

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TomThomson SSC Guru Points: 104773 More actions · Answer 8

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