DDL Trigger

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.
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.

…happened to a SQL Agent job running during the evening.

(more…)

Continue ReadingZero footprint Server/Database DDL Trigger