DDL Trigger for Drop_Table

  • monica.parsi

    SSC Enthusiast

    Points: 167

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

  • TomThomson

    SSC Guru

    Points: 104773

    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

    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

    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: 27168

    Interesting one. Thanks.

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

  • domenico.delbrocco

    Mr or Mrs. 500

    Points: 587

    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: 64685

    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: 64685

    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

    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

    SSChampion

    Points: 10230

    Great question! Thanks!!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182523

    Good question, thanks!

    Made me think a bit 😀

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    great question..

    thanks Mounika.

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Interesting question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • trilist

    SSC Eights!

    Points: 902

    Great question! Thanks.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Had to reread some of the comments.

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

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