Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

DDL trigger Expand / Collapse
Author
Message
Posted Thursday, May 27, 2010 3:46 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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!
Post #928845
Posted Thursday, May 27, 2010 5:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 12, 2011 5:09 AM
Points: 28, 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
Post #928910
Posted Thursday, May 27, 2010 7:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:32 AM
Points: 2,917, Visits: 2,528
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
Post #928981
Posted Thursday, May 27, 2010 7:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 2,387, Visits: 2,776
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


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

"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
Post #928982
Posted Thursday, May 27, 2010 8:45 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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).
Post #929036
Posted Thursday, May 27, 2010 8:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 2,387, Visits: 2,776
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


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

"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
Post #929044
Posted Thursday, May 27, 2010 9:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #929074
Posted Thursday, May 27, 2010 9:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Beautiful question, well done



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #929097
Posted Thursday, May 27, 2010 10:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:27 PM
Points: 271, Visits: 380
Good question!! Thank you.
Post #929142
Posted Thursday, May 27, 2010 11:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #929172
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse