SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DDL trigger


DDL trigger

Author
Message
vk-kirov
vk-kirov
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5658 Visits: 4408
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
muhammad.mazhar
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 50
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
sjimmo
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7401 Visits: 2913
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
webrunner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16928 Visits: 4143
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 says Can I join you?
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
vk-kirov
vk-kirov
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5658 Visits: 4408
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).
webrunner
webrunner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16928 Visits: 4143
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 says Can I join you?
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Tom Garth
Tom Garth
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3509 Visits: 1499
Great question, and for me, educational.

Thanks,

Tom Garth
Vertical Solutions

"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
Paul White
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80514 Visits: 11400
Beautiful question, well done :-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
natalie.ignatieva
natalie.ignatieva
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 438
Good question!! Thank you.
Hugo Kornelis
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34640 Visits: 13125
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: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search