I think a lot of people would find it very helpful SQL Server had what I'm going to call a "VALIDATE TRIGGER" for discussion's sake in this request.
A VALIDATE TRIGGER would be a special case of a BEFORE TRIGGER that exists on a table, and it could be for VALIDATE INSERT, UPDATE and/or DELETE. There can be only one VALIDATE TRIGGER per DML event. For example, if there is a already a "VALIDATE INSERT, UPDATE" trigger on a given table, a subsequent "CREATE... VALIDATE UPDATE" statement on that same table would fail.
It would have "BEFORE" semantics. It would run after check constraints and declarative referential integrity (DRI), but before any data modification.
It would have the [inserted] and [deleted] pseudotables.
Inside the body of the trigger only SELECT statements may be executed against permanent tables and calling-scope temporary tables. Table variables and temporary tables scoped to the trigger body may have data modification.
RAISERROR has special behavior in a VALIDATE TRIGGER. If a RAISERROR statement is executed in a VALIDATE TRIGGER, when the trigger exits, the trigger causes the same statement execution termination behavior as failing a check constraint or failing DRI. The trigger author has the option of allowing multiple RAISERROR statements to be executed, or executing an automatic RETURN after the first RAISERROR statement executed. After a DML statement on the table when a VALIDATE TRIGGER executed one or more RAISERROR statements, @@ERROR will have the error code for the most recently executed RAISERROR statement.
This would have the benefit of allowing triggers to enforce complex business rule validation with the performance enhancement of preventing SQL Server from consuming resources doing data modifications that would ultimately be rolled back from an AFTER trigger. By having the limitation of no permanent table data modification, complex data scenarios are avoided.
See examples in "Proposed Solution" section.
Upvotes: 1<=-=Jul 6 2016 11:38AM=-=>
I think this is done with INSTEAD OF triggers. One can build any validate logic there.
See https://technet.microsoft.com/en-us/library/ms175521%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396 and https://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx
The problem with using INSTEAD OF triggers is that you have to rewrite all the INSERT, UPDATE and DELETE statements inside the trigger. INSERT statements are not too bad because you can just write “INSERT dbo.MyTable SELECT * from inserted;” DELETE statements only require a primary key join between the real table and the deleted pseudo table. The real problem is UPDATE statements. You have to hand code a SET assignment for each non-primary key column. That work is bad enough, but the real problem is application reliability: every time a new column is added to the table, someone has to remember to go into the trigger and add the column to the UPDATE statement.
I guess if one wants make-work for job security, INSTEAD OF triggers are a god-send. My job security is based on rapid development of highly reliable applications.<=-=Jul 7 2016 7:35PM=-=>
OK, I have an idea. I think this would help in a lot of INSTEAD OF trigger situations. Create a new statement that is available only in INSTEAD OF triggers. It might be something like “EXECUTE TRIGGER_STATEMENT”. When executed, SQL Server goes ahead and finishes executing the statement that fired the trigger. That would save a lot of statement rewriting. That way developers could put in all their validation code, and if it all passes, they can just flow control to an EXECUTE TRIGGER_STATEMENT statement. That would make INSTEAD OF triggers much more robust in the face of table column changes.<=-=Mar 6 2017 3:13AM=-=>
This item does not get votes so I would recommend to refine the idea (since you already have some options).
Few comments – Regarding hand-coded select, I believe that you can easily generate it using sys.columns and STRING_AGG. If your concern is table schema changes then you can use SCHEMABINDING. I understand that this is not exactly the solution for your scenario, but we want to fit into existing syntax if possible, and focus on thing that cannot be added wit user code.
I would event propose some statement like <<proceed()>> inside INSTEAD OF TRIGGER that will execute original statement that is replaced by the trigger. This way you could write some code in INSTEAD OF trigger, and if all conditions are fine, you can call “proceed with original” to update rows using original statement. These are just some ideas.
I’m closing this one because this item don’t gets votes and it will not be implemented in this shape.<=-=Mar 6 2017 1:53PM=-=>
Thank you for the suggestions (and understanding my idea). I’ll post a new topic.