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.

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

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

-------------------"Operator! Give me the number for 911!" - Homer Simpson

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:

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:

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

-------------------"Operator! Give me the number for 911!" - Homer Simpson