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!