DDL trigger

Question

DDL trigger

Viewing 12 posts - 16 through 26 (of 26 total)

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

webrunner SSC-Dedicated Points: 31718 More actions · Answer 1

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 asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Tom Garth SSCertifiable Points: 6193 More actions · Answer 2

Great question, and for me, educational.

Thanks,

Tom Garth
Vertical Solutions[/url]

"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 SSC Guru Points: 150467 More actions · Answer 3

Beautiful question, well done 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

natalie.ignatieva SSChasing Mays Points: 622 More actions · Answer 4

natalie.ignatieva

SSChasing Mays

Points: 622

May 27, 2010 at 10:53 am

#1173456

Good question!! Thank you.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 5

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: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

great question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 7

Hugo Kornelis (5/27/2010)
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.

Firm but fair :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 8

I got it right for the wrong reason.

DDL has never been my strong suit. This question came with some good background reading that was very educational.

Thanks.

magasvs SSCertifiable Points: 7660 More actions · Answer 9

Great question! I missed rollback and got it wrong.

UMG Developer SSChampion Points: 13482 More actions · Answer 10

UMG Developer

SSChampion

Points: 13482

June 1, 2010 at 8:33 pm

#1175072

Thanks, learned another thing today!

emginet-874462 SSC Veteran Points: 217 More actions · Answer 11

After Execution Error through

(1 row(s) affected)

Msg 208, Level 16, State 1, Procedure test_ddl, Line 6

Invalid object name 'test_logs'.

(1 row(s) affected)

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Terrific question.

My brain was not working correctly - for some reason I decided that since DDL triggers (unlike DML triggers, which have an "instead" option) always execute after the DDL statement that fires them has completed, in autocommit mode they would execute after the DDL statement had committed - a crazy aberration! I knew it was wrong as I clicked the submit button, because I suddenly remembered that the DDL trigger example everyone uses is the one that displays an error message and rolls back a DROP, and this only works because the trigger and the DROP are parts of the same autocommit unit (transaction). Oh well, I won't forget that as easily again so the question has done me some good (and was fun to think through).

Tom