Add BEFORE triggers - not the least for DDL statements - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 361223 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 8/10/2008 3:27:31 PM
Access Restriction Public


BEFORE triggers is a feature that is sorely missing from SQL Server. BEFORE triggers are very useful to prevent something from happening. Today you can do this with AFTER triggers, but that means that the error has to be rolled back which can be costly. To avoid that issue, you can use INSTEAD OF triggers, but then you need to redo the command, which is complicated and risky.

This is particularly important for DDL triggers. If you want to prevent indexes from being created during peak hours there is no way to prevent this today. If you create a DDL trigger 
that rolls back the index, you only double the damage of the index creation.

Note: there is an existing Connect item,
that also calls for BEFORE triggers, but that item also calls for PER ROW triggers, a 
feature that I'm cool to. And I also would like to stress the importance that BEFORE
triggers is implemented for DDL. (In fact, if you have to make cuts, I think BEFORE triggers
for DDL is important than for DML.)

Yet a point is that BEFORE triggers makes it possible for "On change - Prevent" in the
PBM to work as advertised.
Sign in to post a comment.
Posted by Skazimax on 10/31/2011 at 4:45 AM
Agreed, that this item need to be reviewed by Microsoft again, at least they should say that they are planning to implement this, or they are not planning.
Posted by Pawel Potasinski on 9/29/2011 at 11:44 PM

I would reopen this item. Many of my clients complain about BEFORE triggers missing in SQL Server. These triggers would be especially useful for those clients who host many databases of their customers and would like to keep the control over database configuration. Any reactive mechanism is no good here. Please, consider reopening.

Pawel Potasinski, SQL Server MVP
Posted by Tobias [MSFT] on 3/17/2011 at 10:38 AM
Hej Erland,

Thank you for submitting this suggestion, but we're trying to clean house and remove items we feel we will likely not address given their priority relative to other items in our queue. We believe it is unlikely that we will address this suggestion, and so we are closing it as “won’t fix”.

This cleaning will help us focus on the high-priority items that we feel need to get done, and we hope that it help provide better clarity to you about the issues we will (and won't) address.

While we've tried to look at each of these items individually, this was a large effort, and so we may have erred in assessing this particular suggestion's priority. If you feel that this is worth reconsidering, please feel free to re-file it and we will be happy to take another look.

- Tobias, SQL Server Engine
Posted by Hugo Kornelis on 3/11/2010 at 1:40 AM
My UP vote is to support BEFORE DDL triggers. I don't see quite as much need for BEFORE DML triggers.
Posted by Robson Brandão on 5/13/2009 at 4:30 AM
Before DDL Trigger is very important. Imagine a user that drop a store procedure. With before DDL trigger we can capture the code of procedure and stored on a table to future query.
Posted by Umachandar [MSFT] on 8/11/2008 at 2:26 PM
Hi Erland,
Thanks for your suggestion. We will consider it for a future version of SQL Server. I will also link this request with the existing one about BEFORE triggers which you pointed out already.

Umachandar, SQL Programmability Team