DDL trigger

Question

Post reply

DDL trigger

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

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 1

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: 150468 More actions · Answer 2

Beautiful question, well done

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

natalie.ignatieva SSChasing Mays Points: 632 More actions · Answer 3

natalie.ignatieva

SSChasing Mays

Points: 632

May 27, 2010 at 10:53 am

#1173456

Good question!! Thank you.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

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 5

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

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
All articles available on SQL.kiwi
@SQL_Kiwi

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

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 8

Great question! I missed rollback and got it wrong.

UMG Developer SSChampion Points: 13482 More actions · Answer 9

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 10

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 11

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

DDL trigger

Cookies on SQLServerCentral