Zero footprint Server/Database DDL Trigger
Aaron Bertrand has written a great introduction to server/database DDL triggers in his article SQL Server DDL Triggers to Track All Database Changes.
I wanted to implement similar DDL triggers on more or less all servers. Some of the databases on those servers were from a 3:rd party, so changes in those databases wasn’t possible. Therefore I wanted to make sure these DDL triggers were “zero footprint”. That is, not causing existing database code to fail. I simply added TRY and an empty CATCH to the trigger, believing I’d be safe. Unfortunately, an error like this…
![Msg 3616, Level 16, State 2, Procedure SP1, Line 5 An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.](http://media.tomaslind.net/2013/10/Msg3616.jpg)
…happened to a SQL Agent job running during the evening.