MCTS 70-433 DDL Triggers

There limitation to using DDL Triggers to rollbck transactions:

Steps to Reproduce

1. Execute the following code:

CREATE TRIGGER preventalter

ON ALL SERVER

FOR ALTER_DATABASE

AS

PRINT 'You are in production. Alter database not allowed.'

ROLLBACK TRANSACTION

GO

2. Now change the recovery model

Actual Results

DDL trigger fires as evidenced by the error message being displayed. However, the recovery model is still changed.

Expected Results

DDL trigger fires, displays error message, and prevents the recovery model from being changed.

The problem is:

Most operations for ALTER DATABASE are not transacted operations, and so they do not adhere to transactions. ALTER DATABASE also cannot be run in a user transaction. The fact that the recovery mode change happened in this case is expected behavior.

 Posted by Microsoft on 8/31/2006 at 3:34 PM

However for other transactional operations like creating stored procedures, DDL trigger to allow you to build in validation routines.