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, http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=285655
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.