On 05-05-2021 18:25, Pól Ua L. via Firebird-devel wrote:
So, it appears that the CHECK constraint creates a TRIGGER that is a
BEFORE INSERT one - I think (at least for this use case), it should be
an ON INSERT or AFTER INSERT trigger so that the SUM(loan_amount) GROUP
BY manager_name can never go above 100 which is the desired outcome.
Maybe if the DBA/dev could put the KEYWORD [BEFORE | ON | AFTER] for
SQL in CHECK constraints which contain SQL?
If this is the wrong place to suggest this, please let me know where I
should post this.
Doing this is an abuse of check constraints. Check constraints are
intended to validate a row, they are not intended to handle invariants
for a (subset of) a table. The fact that you can execute selects and run
totals on other rows in a check constraint, does not mean you should.
For this purpose, create an AFTER INSERT OR UPDATE trigger.
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
