DDL trigger

  • webrunner

    SSC-Dedicated

    Points: 30351

    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

    ON DATABASE

    FOR DDL_TABLE_EVENTS

    AS

    SET NOCOUNT ON

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

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

    GO

    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

  • Tom Garth

    SSCertifiable

    Points: 6173

    Great question, and for me, educational.

    Thanks,

    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
  • Paul White

    SSC Guru

    Points: 150442

    Beautiful question, well done 🙂

  • natalie.ignatieva

    SSChasing Mays

    Points: 622

    Good question!! Thank you.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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?

    Regards,

    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/

  • SQLRNNR

    SSC Guru

    Points: 281252

    great question

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

  • Paul White

    SSC Guru

    Points: 150442

    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:

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    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.

    Thanks.

  • magasvs

    SSCertifiable

    Points: 7659

    Great question! I missed rollback and got it wrong.

  • UMG Developer

    SSChampion

    Points: 13482

    Thanks, learned another thing today!

  • emginet-874462

    SSC Veteran

    Points: 217

    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)

  • TomThomson

    SSC Guru

    Points: 104773

    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).

    Tom

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

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