I have always wished SQL Server had BEFORE triggers to implement efficient custom validation.
Doing validation code with standard AFTER triggers has the downside that the validation only comes after the database engine has expended all the resources of executing the triggering statement, and if the validation fails, then the database engine has to expend resources performing the rollback.
Doing validation code with INSTEAD OF triggers avoids the resource issue, but then getting the original statement to execute has issues.
1) You could hard code a statement that copies/deletes the data from the inserted/deleted pseudo tables to the real table. However if the columns of the table change, someone has to remember to alter the trigger to match [and we all frequently forget about the triggers :-)].
2) You could maybe use some sort of dynamic SQL to automatically generate the statement that copies/deletes the data from the inserted/deleted pseudo tables to the real table. However, that carries the potential problem of broken ownership chaining. Also, the deleted and inserted pseudo tables are out of scope in dynamic SQL.
I propose that there be a new statement, something like "EXECUTE ORIGINAL_STATEMENT," that is available only in INSTEAD OF triggers. When executed, SQL Server goes ahead and finishes executing the DML statement that fired the trigger. This would eliminate the issues identified above. With this, developers could put in their validation code, and if it all passes, they just flow control to an EXECUTE ORIGINAL_STATEMENT statement; otherwise they can flow control to a RAISERROR statement.
I think this would be an excellent way to give SQL Server developers a lot of the power of BEFORE triggers in an easy-to-use way without major changes to the SQL Server database engine.