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

  • Article

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:

CREATE TRIGGER ddl_ss_tr_no_alter_db

ON ALL SERVER

FOR ALTER_DATABASE

AS

PRINT 'No ALTER DATABASE allowed!'

ROLLBACK TRAN;

GO

If you execute an ALTER DATABASE – you will indeed get an error message that looks like your operation was prevented – but this is not actually the case.  For example – let’s say I want to increase a file size:

ALTER DATABASE [MR1] MODIFY FILE ( NAME = N'MR1_2', SIZE = 70600KB )

This returns:

No ALTER DATABASE allowed!

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

But if I check the file size – sure enough – my ALTER DATABASE MODIFY FILE was actually executed and not aborted. 

Why?  Some DDL operations aren’t allowed to be rolled back in an atomic fashion in line with the DDL trigger.  For example – DROP DATABASE can be prevented, but not ALTER DATABASE.  At first I thought that the following BOL topic covers – in a roundabout way – what won’t be honored for rollbacks: “Transact-SQL Statements Allowed in Transactions” – however DROP DATABASE is on this list, so this isn’t consistent with what one sees with DDL triggers.

So my recommendation is as follows… If you are expecting a ROLLBACK for your DDL trigger – be sure to test that it actually rolls back the operation.  Don’t rely just on the message back to your client session.  You should actually verify that the operation was rolled back.