Hi all,
I did the following (all the code below is available here):
https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=ddadd69f144fb3341fb4f710a645edac
CREATE TABLE portfolio
(
manager_name VARCHAR (25) NOT NULL,
loan_amount INTEGER NOT NULL CHECK (loan_amount <= 100)
);
populated it:
INSERT INTO portfolio VALUES ('man_1', 20);
INSERT INTO portfolio VALUES ('man_1', 25);
INSERT INTO portfolio VALUES ('man_1', 30);
INSERT INTO portfolio VALUES ('man_2, 99);
I then did:
ALTER TABLE portfolio
ADD CONSTRAINT no_huge_loans_ck_bis CHECK
(
NOT EXISTS
(
SELECT SUM(loan_amount) AS s
FROM portfolio
GROUP BY manager_name
HAVING SUM(loan_amount) > 100
)
);
and thought, wonderful, it works!!!
and then ran:
INSERT INTO portfolio VALUES ('man_1', 90);
this didn't throw an error - and this:
SELECT SUM(loan_amount) AS s
FROM portfolio
GROUP BY manager_name
HAVING SUM(loan_amount) > 100;
gives a result of:
S
165
So, the sum goes over 100 - which is NOT what I want.
Now, I INSERT 1 more Euro as follows:
INSERT INTO portfolio VALUES ('man_1', 1);
fails with the error message:
Operation violates CHECK constraint NO_HUGE_LOANS_CK_BIS on view or table
PORTFOLIO At trigger 'CHECK_3'
Just so we see the state of the table - SELECT * FROM portfolio:
MANAGER_NAME LOAN_AMOUNT
man_1 20
man_1 25
man_1 30
man_2 99
man_1 90
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.
TIA and rgs,
Pól...
Sent with [ProtonMail](https://protonmail.com) Secure Email.Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
