INSTEAD OF DDL Triggers and DMF - by Greg Low - Australia

Status : 


Sign in
to vote
ID 299328 Comments
Status Active Workarounds
Type Suggestion Repros 1
Opened 9/23/2007 11:47:24 PM
Access Restriction Public


I know we've talked about this a number of times with a number of people but I wish to reopen Adam Machanic's 243986 suggestion on the need for INSTEAD OF triggers for DDL operations. DMF now makes the need for these even more pressing. Many operations are way too expensive to roll back and some operations such as CREATE DATABASE are unable to be rolled back.

It should be possible to issue DMF policies for say "you may not rebuild indexes during 9am to 5pm Mon to Fri". 

INSTEAD OF triggers would also allow updating the DDL before executing it. For example, it could be formatted according to an organisation's standard before being executed (and stored). You could also automatically insert comments on who performed the operation into the DDL for CREATE or ALTER. You could potentially even insert a checksum or procedure lineage information automatically.
Sign in to post a comment.
Posted by Adam Machanic on 3/9/2010 at 6:53 AM
Hi Vineet,

Just to add to this, this feature is completely frustrating in its current incarnation. I just had a 12-hour index create get rolled back by one of these. There is no reason that the DDL trigger in question would have been an AFTER trigger had there been a choice. The same logic would have been perfect in an INSTEAD OF trigger, and would have saved a lot of time. This really needs to be addressed ASAP to avoid customer frustrations.

Thank you,
Adam Machanic
Posted by Microsoft on 11/14/2007 at 1:48 PM
Hi Greg,

Thank you for sending us your feedback. We have got several requests in the past about INSTEAD OF DDL triggers and we will consider adding it in a future release of SQL Server.

-Vineet Rao