Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

INSTEAD OF DDL Triggers and DMF

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.

0 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 56

    <=-=Nov 14 2007 1:48PM=-=>

    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.

    Thanks,
    -Vineet Rao

    <=-=Mar 9 2010 6:53AM=-=>

    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

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base