BEFORE TRIGGER Behavior with EXECUTE ORIGINAL_STATEMENT
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.
Upvotes: 32<=-=Apr 3 2017 6:09AM=-=>
Thanks to this idea. We understand the requirement and we are putting it into the backlog. Since we don’t have plans to work on trigger enhancements in the near future, we cannot guarantee when we will implement something like this. If this feature gets more votes it would be reconsidered in future releases.<=-=May 20 2017 1:16PM=-=>
Not sure who voted this one down but they could at least have had the nads to explain why they voted it down.
The reason I voted it up is because, as it has in Oracle, it would allow the addition of logging columns and validation before writing to a table and then rolling back. The “Instead of” triggers require you to take full control and frequently cause an update to columns that didn’t need to be updated. Written to operate like the “Before” triggers in Oracle, you could avoid all of that. I’ve never understood why SQL Server didn’t have the capability.<=-=May 21 2017 2:03AM=-=>
I upvoted this. EXECUTE ORIGINAL_STATEMENT in INSTEAD OF triggers sounds like a good workaround for the lack of actual BEFORE triggers.
However, it is still a workaround, and I would prefer even more if Microsoft would simply add BEFORE triggers to the product.
(Oh, and by the way – on Firefox the text in the description does not wrap; I had to switch to IE just to read the full description. Can someone please get the Connect team to include Firefox in their test matrix? It’s not like I’m using a niche browser here…)<=-=May 21 2017 7:27AM=-=>
I voted it up because it opens up validation at the database level. Besides, if you have AFTER triggers, having BEFORE triggers is just logical.
I know you know it already, but Oracle has had BEFORE triggers for years.
There were 40+ up-votes on this idea in Connect