Re: Enforce business rule with before insert trigger?
I agree that you could use this to prevent the transaction from being committed.
(you can also call an UDF that does nothing but raise an error; this works, I checked)
Still, the challenge is to come up with something that will emulate a "AFTER .. STATEMENT" trigger.
So far, I can think of nothing to solve the actual, literal requirements. However, I think it could be solved functionally, at least more than half-way down the road. IMO, there are three straigtforward scenarios:
1) If the application is able to come up with a single INSERT that contains (or generates) all of the rows for the detail, than the application can always perform this check itself. I agree right away that it is not desirable to have this kind of logic in the application, but it would solve the matter.
2) I believe that when the requirement of having the check done by the database server, and not in the application is really very important, there is a workaround. It requires we adjust the requirements just a little bit. Suppose we'd write a procedure that performs the check on a temporary table. We could have the application insert the rows in a temporary table, perform the check and let the procedure decide wheter to raise an error or insert the rows from the temporary table in the actual persistent table.
(A modified version could employ dynamic sql, and have the application pass all its data in a string format. That way, the application would have to perform but a single CALL to the procedure instead of first an INSERT and then a CALL).
The proc (and the temporary table) would be defined with DEFINER privileges, and granted to the application user. Access to the persistent table must not be granted to the ordinary application user - this makes the proc the only portal to insert detail rows.
3) You do not check the validity of the order when adding detail rows, but when the order as a whole is processed further. Although you could update a flag reflecting the validity of the order as a whole, there's not much benefit in that (as you can always recalculate it from the detail rows at any time).
The actual check can (and should) of course be wrapped inside a stored procedure, but the application must be modified so that it actually calls the procedure when it is needed. This is quite similar to the 1st solution, but with the benefit that the application is only responsible for handling the event logic - not the actual business rule.
---
I haven't worked it out in code, but I got a hunch that it should be possible to emulate the after statement trigger. It's going to be ugly though. The idea is that you could maintain a list of records 'in the works'.
Let's say we have a table to store the primary key (assume it's a plain unsigned INT), along with the SESSION_ID() value. Now, if we have both a BEFORE INSERT and BEFORE UPDATE trigger that puts primary key values into the table and both a AFTER INSERT and AFTER UPDATE trigger that takes them out of the table you have a mechanism to check wheter the statement as a whole will be finished.
Let's simplify and assume we are only dealing with normal, traditional UPDATEs and INSERTs.
In principle, each BEFORE INSERT will be matched by exactly one AFTER INSERT, and each BEFORE UPDATE will be matched by exactly one AFTER UPDATE - all for the current session of course.
Now, if we would insert one key into our list for the BEFORE event, and take one (matching) key out of the list in the corresponding AFTER event, we can detect when the statement as a whole is complete: this will be when our list is empty. So, if we'd put a AFTER DELETE trigger on our list, this would check if the list as a whole is empty. If it is, we are AFTER the STATEMENT and we can start checking our business rule. This would then either raise the error or allow a subsequent commit.
Nasty eh? Anyway, just some thoughts.
Edited 1 time(s). Last edit at 01/04/2006 06:05PM by Roland Bouman.