OK,
MESS!!!!
Somewhere something went wrong.
While writing and working on the TRIGGER, something got lost in translation, and what should have been, changed to something that does not make sense.
The TRIGGER is supposed to fire for CertificateSupplierAbbreviation-CertificateCode.
(Thanks eremmel for pointing it out, but I did not see it at first).
This is what the criteria are:
1. CertificateSupplierAbbreviation-CertificateCode: UNIQUE
2. CertificateSupplierAbbreviation can exist without CertificateSupplierShown
3. No CertificateSupplierShown if no CertificateSupplierAbbreviation
4. Both CertificateSupplierShown and CertificateSupplierAbbreviation can be empty
5. A Person must be entered at all times under any circumstance (this is not a part of the TRIGGER, but important for further explanation)
Examples of Certificates entered in tParticipantProjectCertificate are:
(1st: Certificate entered; 2nd: Certificate Shown)
a. PUD 231qwa; PUD 231qwa
b. AR 23456; 23456
c. 1779TJ-QSO
d. NULL
Both a. and b. are to be UNIQUE, because the TRIGGER must test against criterium 1.
The c.-entry, however, is entered without a CertificateSupplier, and therefore must NOT cause the TRIGGER to fire. This is caught by the statement:
Code: Select all
"Existing"."CertificateSupplierAbbreviation" IS NOT NULL
The d.-entry is entered into the tParticipantProjectCertificate table without any CertificateSupplier and CertificateCode (both are NULL).
This is a special situation, where a FKPersonID can be entered, and where no CertificateSupplierAbbreviation-CertificateCode is required (see also 5th criterium).
This entry must NOT cause the TRIGGER to fire. This is caught by the statement:
Code: Select all
"Existing"."CertificateCode" IS NOT NULL
It also prevents the TRIGGER to go through all the entries in the table tParticipantProjectCertificate (which is now close to 7000 entries and growing steadily).
The table tCertificateSupplier is a static table. Entries/Updates are made by the function responsible for maintaining this table (in this case someone within Business Administration department).
The record CertificateSupplierShown is there for the use in Queries (See Sample DataBase attached).
It has no other function.
This is the updated code
Code: Select all
CREATE TRIGGER PUBLIC.CERTIFICATE_CHECK BEFORE INSERT ON PUBLIC."tParticipantProjectCertificate"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW BEGIN ATOMIC
IF EXISTS
(
SELECT 1
FROM
(
SELECT
COALESCE("tCertificateSupplier"."CertificateSupplierAbbreviation",'') "CertificateSupplierAbbreviation",
NEWROW."CertificateCode" "CertificateCode"
FROM
"tProjectInfo"
LEFT JOIN "tCertificateSupplier" ON
"tProjectInfo"."FKCertificateSupplierID" = "tCertificateSupplier"."CertificateSupplierID"
WHERE
"tProjectInfo"."ProjectInfoID" = NEWROW."FKProjectInfoID"
) "NewEntry"
INNER JOIN
(
SELECT
COALESCE ( "tCertificateSupplier"."CertificateSupplierAbbreviation", '' ) "CertificateSupplierAbbreviation",
"tParticipantProjectCertificate"."CertificateCode" "CertificateCode"
FROM
"tProjectInfo"
LEFT JOIN "tCertificateSupplier" ON
"tProjectInfo"."FKCertificateSupplierID" = "tCertificateSupplier"."CertificateSupplierID",
"tParticipantProjectCertificate" "tParticipantProjectCertificate"
WHERE
"tParticipantProjectCertificate"."FKProjectInfoID" = "tProjectInfo"."ProjectInfoID"
) "Existing"
ON
"NewEntry"."CertificateCode" = "Existing"."CertificateCode" AND
"NewEntry"."CertificateSupplierAbbreviation" = "Existing"."CertificateSupplierAbbreviation"
WHERE
"Existing"."CertificateCode" IS NOT NULL AND
"Existing"."CertificateSupplierAbbreviation" IS NOT NULL
)
THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='This Certificate already exists, please enter a different Certificate!';
END IF;
END
I am now in the process of stress-testing, to find out if this version is working.
NOTE to self: Make sure if you try to ask for help, ask the "right" question
Hope this explains more,
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.