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.
No comments:
Post a Comment