Gotcha: When Server-Scoped DDL Triggers don’t honor ROLLBACK

I’ve used DDL Triggers before to prevent database DROP DATABASE operations.  For example:

           CREATE TRIGGER ddl_ss_prevent_database_drop
ON ALL SERVER
FOR DROP_DATABASE
AS
PRINT 'Will not drop database until you disable this trigger.'
ROLLBACK
GO

If you try to drop a database with this DDL trigger in place, you’ll get the following message:

Will not drop database until you disable this trigger.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

What’s more – your DROP DATABASE truly is prevented, as one would expect. But this behavior isn’t universal across all operations.  Sometimes ROLLBACK isn’t possible.  Take the following example of a DDL trigger on an ALTER_DATABASE event

Read more: Joe Sack's SQL Server WebLog

Posted via email from jasper22's posterous