DDL trigger

  • vk-kirov

    SSCertifiable

    Points: 7686

    Comments posted to this topic are about the item DDL trigger

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    good question.

    when SET IMPLICIT_TRANSACTIONS off it will shows two times and other wise if we put SET IMPLICIT_TRANSACTIONS on then second time it shows "invalid object".This is due to SET IMPLICIT_TRANSACTIONS off.

    good explanation.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • This was removed by the editor as SPAM

  • honza.mf

    SSCertifiable

    Points: 5519

    Great question.

    The first part with deffered table name was easy. I missed the second part - the table is not dropped due to transaction.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    I do not fully understand.

    The batch begins with:

    SET IMPLICIT_TRANSACTIONS OFF

    According to BOL, this means the following:

    ... SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.

    However, the explanation says the following:

    When the DROP TABLE statement is executed, an implicit transaction occurs. The DROP statement and the DDL trigger are run within that transaction.

    What am I missing here? Is the DROP TABLE statement and the trigger regarded as an individual statement in autocommit mode?

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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    da-zero (5/27/2010)


    I do not fully understand.

    The batch begins with:

    SET IMPLICIT_TRANSACTIONS OFF

    According to BOL, this means the following:

    ... SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.

    However, the explanation says the following:

    When the DROP TABLE statement is executed, an implicit transaction occurs. The DROP statement and the DDL trigger are run within that transaction.

    What am I missing here? Is the DROP TABLE statement and the trigger regarded as an individual statement in autocommit mode?

    Note that IMPLICIT_TRANSACTION OFF is the default; vk-kirov could have omitted this and mentioned "default settings" in the text for the same effect.

    Regardless of this setting, every statement that modifies anything in the DB is always in a transaction. If no transaction is active at the start of a statement, SQL Server will implicitly start a transaction. The IMPLICIT_TRANSACTION setting governs what happens after the statement finishes - OFF means auto-commit (i.e., the transaction that was started implicitly will end implicitly after the statement finishes), ON means manual commit (i.e. even if the transaction was implicitly started, it will remain open until either an explicit rollback or commit, or a rollback caused by error).

    If you change the QotD to IMPLICIT_TRANSACTIONS ON (don't forget to drop the database trigger and then the table first), the second SELECT will return an error. Not because this setting causes the DROP TABLE to suddenly fail - there still is an error in the trigger, that will still cause the transaction to be rolled back. Only now, this is the transaction that was implicitly started by the first modifying statement (the CREATE TRIGGER statement), which was not yet committed. The DROP TABLE still fails, but because the CREATE TABLE is rolled back as well, the effect is that the table is gone.

    @vk-kirov: Great 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/

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Allright Hugo, thanks for the explanation!

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

  • sharath.chalamgari

    SSCertifiable

    Points: 5680

    Good Question vk-kirov

    i learned somthing about IMPLICIT_TRANSACTIONS

  • Maddy...!

    SSC Eights!

    Points: 995

    yah... really a good question and i got some info reading implicit transactions need 2 read jst think it to be the kick start

    still u have this sort of question plz post my dear co db kings...........

  • ziangij

    SSCertifiable

    Points: 7239

    thanks for this... i had a hard time cleaning this one 🙂

    by mistake, I executed this in the UAT environment and then i began searching the syntax for dropping a trigger...

    DROP TRIGGER triggy ON DATABASE;

  • vk-kirov

    SSCertifiable

    Points: 7686

    Hugo Kornelis (5/27/2010)


    Note that IMPLICIT_TRANSACTION OFF is the default; vk-kirov could have omitted this and mentioned "default settings" in the text for the same effect.

    When I was writing the question, I remembered that someone had said (in a QOTD discussion) about his/her IMPLICIT_TRANSACTION option set to ON. And the result of the query had been different from that QOTD answer. This is why I decided to add the IMPLICIT_TRANSACTION statement in the script. And now I think it is another learning aspect of this QOTD: an implicit transaction may occur even with IMPLICIT_TRANSACTIONS = OFF, which was new to some respondents.

    Hugo Kornelis (5/27/2010)


    @vk-kirov: Great question!

    Thanks! And thank you for the excellent (as always) explanation! 🙂

  • muhammad.mazhar

    SSC Veteran

    Points: 290

    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

  • sjimmo

    SSChampion

    Points: 11139

    vk - Excellent question. I got it wrong, then had to re-read the question. Next time I should make sure that I read the question thoroughly.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • webrunner

    SSC-Dedicated

    Points: 30363

    Hugo Kornelis (5/27/2010)


    da-zero (5/27/2010)


    I do not fully understand.

    The batch begins with:

    SET IMPLICIT_TRANSACTIONS OFF

    According to BOL, this means the following:

    ... SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.

    However, the explanation says the following:

    When the DROP TABLE statement is executed, an implicit transaction occurs. The DROP statement and the DDL trigger are run within that transaction.

    What am I missing here? Is the DROP TABLE statement and the trigger regarded as an individual statement in autocommit mode?

    Note that IMPLICIT_TRANSACTION OFF is the default; vk-kirov could have omitted this and mentioned "default settings" in the text for the same effect.

    Regardless of this setting, every statement that modifies anything in the DB is always in a transaction. If no transaction is active at the start of a statement, SQL Server will implicitly start a transaction. The IMPLICIT_TRANSACTION setting governs what happens after the statement finishes - OFF means auto-commit (i.e., the transaction that was started implicitly will end implicitly after the statement finishes), ON means manual commit (i.e. even if the transaction was implicitly started, it will remain open until either an explicit rollback or commit, or a rollback caused by error).

    If you change the QotD to IMPLICIT_TRANSACTIONS ON (don't forget to drop the database trigger and then the table first), the second SELECT will return an error. Not because this setting causes the DROP TABLE to suddenly fail - there still is an error in the trigger, that will still cause the transaction to be rolled back. Only now, this is the transaction that was implicitly started by the first modifying statement (the CREATE TRIGGER statement), which was not yet committed. The DROP TABLE still fails, but because the CREATE TABLE is rolled back as well, the effect is that the table is gone.

    @vk-kirov: Great question!

    Thank you for the explanation, Hugo.

    I have one question. This is what I got when I ran the script from the question (in SQL 2005), using Results to Text.

    log_message

    ----------------------------------------------------------------------------------------------------

    test_ddl trigger fired

    (1 row(s) affected)

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

    Invalid object name 'test_logs'.

    log_message

    ----------------------------------------------------------------------------------------------------

    test_ddl trigger fired

    (1 row(s) affected)

    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?

    Thanks,

    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

  • vk-kirov

    SSCertifiable

    Points: 7686

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

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

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