DDL trigger

  • vk-kirov (5/27/2010)

    webrunner (5/27/2010)

    The invalid object error is in there, but the SELECT statements agree with the SELECT statement results presented in the QOTD correct answer. What statement does the invalid object error correspond to - the INSERT statement in the trigger?

    Exactly. When SQL Server executes the INSERT statement, the table 'test_logs' doesn't exists because it's been dropped by the DROP TABLE statement. You may modify the trigger as follows:

    CREATE TRIGGER test_ddl





    SELECT * FROM sys.tables WHERE name = 'test_logs'

    INSERT test_logs (log_message) VALUES ('test_ddl trigger fired')


    This trigger returns 1 row when the CREATE TABLE statement is executed, and 0 rows when the DROP TABLE statement is executed (which means the table 'test_logs' doesn't exist).

    Thanks for confirming that about the error. And thanks again for the excellent question. I definitely need to expand my knowledge of DDL triggers and implicit transactions. I think I learned more by getting it wrong than I would have if I had guessed correctly.

    - webrunner

    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Great question, and for me, educational.


    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Beautiful question, well done 🙂

  • Good question!! Thank you.

  • muhammad.mazhar (5/27/2010)

    i chose the last answer, cause this is what happend to me. But my answer is wrong. I don't understand. what I'm missing here?


    Mazhar Karimi

    What you are missing is that the question was not "what happens if you run this code", but "what is the result of the SELECT statements". The error you do see is caused by the trigger.

    Or, another way to put it, you are missing that the point of these questions is to think about it, not to test your ability to copy/paste.

    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/

  • great question

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hugo Kornelis (5/27/2010)

    Or, another way to put it, you are missing that the point of these questions is to think about it, not to test your ability to copy/paste.

    Firm but fair :laugh:

  • I got it right for the wrong reason.

    DDL has never been my strong suit. This question came with some good background reading that was very educational.


  • Great question! I missed rollback and got it wrong.

  • Thanks, learned another thing today!

  • After Execution Error through

    (1 row(s) affected)

    Msg 208, Level 16, State 1, Procedure test_ddl, Line 6

    Invalid object name 'test_logs'.

    (1 row(s) affected)

  • Terrific question.

    My brain was not working correctly - for some reason I decided that since DDL triggers (unlike DML triggers, which have an "instead" option) always execute after the DDL statement that fires them has completed, in autocommit mode they would execute after the DDL statement had committed - a crazy aberration! I knew it was wrong as I clicked the submit button, because I suddenly remembered that the DDL trigger example everyone uses is the one that displays an error message and rolls back a DROP, and this only works because the trigger and the DROP are parts of the same autocommit unit (transaction). Oh well, I won't forget that as easily again so the question has done me some good (and was fun to think through).


Viewing 12 posts - 16 through 26 (of 26 total)

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