DDL trigger

Question

DDL trigger

vk-kirov

SSCertifiable

Points: 7686
More actions
May 26, 2010 at 9:37 pm

#387027

Comments posted to this topic are about the item DDL trigger
May 27, 2010 at 12:02 am

This was removed by the editor as SPAM

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

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

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 1

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)

honza.mf SSCertifiable Points: 5519 More actions · Answer 2

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: 259105 More actions · Answer 3

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: 64790 More actions · Answer 4

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: 259105 More actions · Answer 5

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 More actions · Answer 6

Good Question vk-kirov

i learned somthing about IMPLICIT_TRANSACTIONS

Maddy...! SSC Eights! Points: 995 More actions · Answer 7

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 SSCrazy Eights Points: 8066 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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: 31862 More actions · Answer 12

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 More actions · Answer 13

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