[Solved] TRIGGER use with a VIEW to prevent duplicates

Discuss the database features
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

chrisb wrote: however i'm posting the code below in order to demonstrate that the value of "tCertificateSupplier.CertificateSupplierShown" is non compulsory in effecting a solution.
It looks like that the issue that you missed is that CertificateSupplierShown can be empty for multiple tCertificateSupplier rows. In that case you need to flag a duplicate as well. But I I'm still not sure if the OP wants to use CertificateSupplierShown or CertificateSupplierAbbreviation as he indicated in his initial posting. In the latter case your code is sufficient.
Unfortunate formulating clear specifications is a craft in itself that not many people conquer.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

A Quick response to eremmel and chrisb.
I am tied up in a lot of other work, but am testing both answers under heavy load conditions.
This process is taking longer than expected as well.

Keep you posted about the outcome.
Thanks for the help so far.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

dreamquartz wrote:A Quick response to eremmel and chrisb.
I am tied up in a lot of other work, but am testing both answers under heavy load conditions.
This process is taking longer than expected as well.

Keep you posted about the outcome.
Thanks for the help so far.

Dream
After a lot of testing I consider the topic solved.
It does appear that once a while a unique CertificateCode slips through, but when caught, it cannot be duplicated.
It appears therefore that the TRIGGER itself does not always check an entry. That might be a bug?
There might be a need to open a new topic.

I cleaned the code up to fit to work, including the use of:

Code: Select all

 "Existing"."CertificateCode" IS NOT NULL AND
 "Existing"."CertificateSupplierAbbreviation" IS NOT NULL
.
The 2 are used to make sure that if there needs to be an unique CertificateCode, and the CertificateSupplier is not to be shown.

The following is the end result:

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"."CertificateSupplierShown",'') "CertificateSupplierShown",
							 NEWROW."CertificateCode" "CertificateCode"
						 FROM
							 "tProjectInfo"
								 LEFT JOIN "tCertificateSupplier" ON
									 "tProjectInfo"."FKCertificateSupplierID" = "tCertificateSupplier"."CertificateSupplierID"
						 WHERE
							 "tProjectInfo"."ProjectInfoID" = NEWROW."FKProjectInfoID"
					 ) "NewEntry"
					 INNER JOIN
					 (
						 SELECT
							 COALESCE ( "tCertificateSupplier"."CertificateSupplierShown", '' ) "CertificateSupplierShown",
							 "tParticipantProjectCertificate"."CertificateCode" "CertificateCode", 
							 "tCertificateSupplier"."CertificateSupplierAbbreviation"
						 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"."CertificateSupplierShown" = "Existing"."CertificateSupplierShown"
				 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 wish to express my sincere thanks to both eremmel and chrisb for helping me to get to this point.
This was one of the most complicated issues I have come across. It has been there since the beginning of development, and no one in our team could get a proper answer.
I still will try to convert it into a similar solution, using a View, where all the information is available in one table. This will clean up the database even more.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: [SOLVED] TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

Your trigger statement might not be correct.
The INNER JOIN condition between "NewEntry" and "Existing" is
"NewEntry"."CertificateCode" = "Existing"."CertificateCode" AND
"NewEntry"."CertificateSupplierShown" = "Existing"."CertificateSupplierShown"
This implies never a match on the situation that "Existing"."CertificateCode" IS NULL. So the extra condition you added on
Existing"."CertificateCode" IS NOT NULL AND "Existing"."CertificateSupplierAbbreviation" IS NOT NULL
can be simplified to
"Existing"."CertificateSupplierAbbreviation" IS NOT NULL
but is that what you want? Do you expect to have a condition like
Existing"."CertificateCode" IS NOT NULL AND "Existing"."CertificateSupplierAbbreviation" IS NOT NULL
and view that as valid?

Make for yourself matrix schemas with {CertificateCode, CertificateSupplierShown, CertificateSupplierAbbreviation } and {NewEntry, Existing} and for each cell put in a { 'Match between NewEntry and Existing', 'No match between NewEntry and Existing', Null value in NewEntry, Null value in Existing, Both are Null }
Then mark all valid combination or blocked combination validate them against the logic of you trigger
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: [SOLVED] TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

eremmel wrote:Your trigger statement might not be correct.
The INNER JOIN condition between "NewEntry" and "Existing" is
"NewEntry"."CertificateCode" = "Existing"."CertificateCode" AND
"NewEntry"."CertificateSupplierShown" = "Existing"."CertificateSupplierShown"
This implies never a match on the situation that "Existing"."CertificateCode" IS NULL. So the extra condition you added on
Existing"."CertificateCode" IS NOT NULL AND "Existing"."CertificateSupplierAbbreviation" IS NOT NULL
can be simplified to
"Existing"."CertificateSupplierAbbreviation" IS NOT NULL
but is that what you want? Do you expect to have a condition like
Existing"."CertificateCode" IS NOT NULL AND "Existing"."CertificateSupplierAbbreviation" IS NOT NULL
and view that as valid?

Make for yourself matrix schemas with {CertificateCode, CertificateSupplierShown, CertificateSupplierAbbreviation } and {NewEntry, Existing} and for each cell put in a { 'Match between NewEntry and Existing', 'No match between NewEntry and Existing', Null value in NewEntry, Null value in Existing, Both are Null }
Then mark all valid combination or blocked combination validate them against the logic of you trigger
There are entries where there will be no CertificateCodes entered. The DataBase is still keeping record of them. These are instructions, forms containing general information, deemed not critical, but simply useful. To be able to enter those documents as well, and not getting a TRIGGER response, this seems to work. Have to look into it with more detail. It might be indicative to the above issue described as a potential 'bug'.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

As indicated, this topic is re-opened again, due to issues when initiating the split database.

After more testing, it turns out that the TRIGGER is not activated the moment the database is opened.
This seems strange, because as far as my knowledge goes, a TRIGGER is read, just like everything else, because it is part of of the "database.script"-file, when a database is opened.

I have to physically make a mistake once, before the TRIGGER fires, but not before that.

Example:
CertificateCode: 690175 has been entered in a previous entry cycle.
Now in a new cycle, new documents with the same CertificateSupplier are to be entered. Therefore it must NOT be possible to re-enter 690175, but I can. After changing to the correct CertificateCode (i.e. 690275), and trying to change again to CertificateCode: 690175, the TRIGGER fires, and shows the pop-up indicating the error message.

Any thoughts?

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

Dear Dream,
Be care full to blame a database product, you need a stronger reproduction scenario than you stated.

I cannot follow your example.
- Your trigger is defined as 'BEFORE INSERT' so how is it possible that it is fired during an update?
- Did you ran the SQL statement that should have been fired after the 'duplicate' insert by hand (just add it to your SQL statements in Base)?
- Your trigger has the functionality to prevent duplicates between two tables based on { CertificateCode, CertificateSupplierShown } via the relation of table tProjectInfo. You are now complaining about a duplicate at CertificateCode. That is another condition that might be solved with a unique constraint
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: TRIGGER use with a VIEW to prevent duplicates

Post by chrisb »

dreamquartz, i cannot replicate your issue when using an insert trigger. it works as expected at all times without issue.

today for the first time i have downloaded your sample database.

to avoid duplicates when inserting a new row into the table "ParticipantProjectCertificateID" the table "tCertificateSupplier" & its fields "CertificateSupplierAbbreviation" & "CertificateSupplierShown" are irrelevant.
why? because the table "tProjectInfo" has the field "FKCertificateSupplierID", a foreign key which references "tCertificateSupplier"."CertificateSupplierID".
when "FKCertificateSupplierID" = 0 then the value held by "CertificateSupplierAbbreviation" will always = 'QAD' & the value held by "CertificateSupplierShown" will always = 'QAD'.
when "FKCertificateSupplierID" = 2 then the value held by "CertificateSupplierAbbreviation" will always = 'AR' & the value held by "CertificateSupplierShown" will always = '' (empty string).
this means that in order to avoid duplicates we only need to focus our attention on two tables & four fields (marked with asterisks below).
"tProjectInfo"
* "ProjectInfoID"
* "FKCertificateSupplierID"

"tParticipantProjectCertificate"
* "FKProjectInfoID"
* "CertificateCode"

below is the code i posted on Wed Feb 22, 2017 3:59 pm. is it effective? if not then why not?

Code: Select all

CREATE TRIGGER A
BEFORE INSERT
ON "tParticipantProjectCertificate"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
BEGIN ATOMIC

IF EXISTS
(
SELECT "CertificateCode" FROM
	 (   
	 SELECT * FROM
			(SELECT "FKProjectInfoID", "CertificateCode" FROM "tParticipantProjectCertificate") A
			JOIN
			(SELECT "ProjectInfoID", "FKCertificateSupplierID" FROM "tProjectInfo") B
			ON B."ProjectInfoID" = A."FKProjectInfoID"
			WHERE "FKProjectInfoID" = NEWROW."FKProjectInfoID" AND "CertificateCode" = NEWROW."CertificateCode"
	 ) A
			JOIN
			(
			SELECT * FROM
				 (SELECT "FKProjectInfoID", "CertificateCode" FROM "tParticipantProjectCertificate") A
				 JOIN
				 (SELECT "ProjectInfoID", "FKCertificateSupplierID" FROM "tProjectInfo") B
				 ON B."ProjectInfoID" = A."FKProjectInfoID"
				 WHERE "CertificateCode" = NEWROW."CertificateCode"
			) B
			ON B."CertificateCode" = A."CertificateCode" AND B."FKCertificateSupplierID" = A."FKCertificateSupplierID"
)
THEN
	 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CERTIFICATE ALREADY EXISTS';
	 END IF;
END
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

@Chrisb
In the above Dream made the following two statements:
  • However, the test for Unique is on CertificateSupplierAbbreviation, because some documents are issued without a CertificateSupplierShown, while they are still have to be considered Unique.
    Leaving CertificatSupplierShown empty, the documents issued can still be verified to be Unique, while they are shown without a Supplier Code.
  • Not quite there yet.
    When looking at your suggestion, I realize that the requirement of what needs to be unique (the combination of "tCertificateSupplier"."CertificateSupplierShown" and "tParticiapntProjectCertificate"."CertificateCode") is not caught.
What is the truth here?
In the latter case there might be multiple records with "tCertificateSupplier"."CertificateSupplierShown" with value empty string. So in the latter case it is not sufficient to validate against equality on tProjectInfo tProjectInfo.FKCertificateSupplierID, because there might be multiple projects that reference different tCertificateSupplier-s that have the same 'empty' CertificateSupplierShown.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: TRIGGER use with a VIEW to prevent duplicates

Post by chrisb »

@eremmel,
i myself have doubts due to the lack of clarity.
the example database 'SampleBase.odb' posted by dreamquartz does however show single static values for both "tCertificateSupplier"."CertificateSupplierAbbreviation" & "tCertificateSupplier"."CertificateSupplierShown".
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

@chrisb, what is more difficult: state what you want or craft a test situation that covers all (allowed) possibilities....
BTW, The data model raises a question: can only one department contribute documents to a project?
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

eremmel wrote:Dear Dream,
Be care full to blame a database product, you need a stronger reproduction scenario than you stated.

I cannot follow your example.
- Your trigger is defined as 'BEFORE INSERT' so how is it possible that it is fired during an update?
- Did you ran the SQL statement that should have been fired after the 'duplicate' insert by hand (just add it to your SQL statements in Base)?
- Your trigger has the functionality to prevent duplicates between two tables based on { CertificateCode, CertificateSupplierShown } via the relation of table tProjectInfo. You are now complaining about a duplicate at CertificateCode. That is another condition that might be solved with a unique constraint
Hi eremmel,
Thanks for your response.
Will reply to them in the same order.

Is the function 'BEFORE INSERT' not to perform the following task:
1. Place information in a temporary record (defined by NEWROW)
2. Execute the criteria within the TRIGGER (in my case compare with existing information)
3. Based on outcome of Execution, perform task (in my case; when "NEW', add info; when "EXISTING", show message).

The TRIGGER is part of the DataBase.script file. Entered via SQL-direct.

I was incomplete with my description. It should have been even completely different (see below). The TRIGGER must be based on CertificateSupplierAbbreviation-CertificateCode.
Something got lost in translation, until you caught it. Thanks for that.
Also, The use of UNIQUE in the table tParticipantProjectCertificate, causes the problem of having 2 of the same CertificateCodes, while the CertificateSupplier are different.
The combination of CertificateSupplierAbbreviation-CertificateCode is the only thing that must be UNIQUE.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

OK,

MESS!!!! :crazy: :knock:
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 :oops:

Hope this explains more,

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

With the specification given above you are not complete:
1. CertificateSupplierAbbreviation-CertificateCode: UNIQUE
So we will not talk about CertificateSupplierShown: irrelevant. But what values can CertificateSupplierAbbreviation have?
i. some text
ii. empty string
iii. NULL
iV. Derived NULL value because tProjectInfo.FKCertificateSupplierID IS NULL

Is there an uniqueness on CertificateSupplierAbbreviation or is there a unique constraint on CertificateSupplierAbbreviation with NULL values excluded?
Which of the four above (i, ii, iii, iv) in combination of CertificateCode has to be validated for unique?
3. No CertificateSupplierShown if no CertificateSupplierAbbreviation
What does this mean?
a. tProjectInfo.FKCertificateSupplierID IS NULL
b. tProjectInfo"."FKCertificateSupplierID IS NOT NULL AND tCertificateSupplier.CertificateSupplierAbbreviation IS NULL
c. tProjectInfo"."FKCertificateSupplierID IS NOT NULL AND tCertificateSupplier.CertificateSupplierAbbreviation = ''
4. Both CertificateSupplierShown and CertificateSupplierAbbreviation can be empty
What does this mean?
d. tCertificateSupplier.CertificateSupplierAbbreviation = ''
(i)The c.-entry, however, is entered without a CertificateSupplier, and therefore must NOT cause the TRIGGER to fire.
(ii)The d.-entry is entered into the tParticipantProjectCertificate table without any CertificateSupplier and CertificateCode (both are NULL).
(i)This implies that you can change the "LEFT JOIN" into an "INNER JOIN" the result of this change is that you only consider defined relations between tCertificateSupplier and tParticipantProjectCertificate.
(ii) You are joining on "NewEntry"."CertificateCode" = "Existing"."CertificateCode", this condition is not satisfied when any of the two IS NULL.
So both conditions in the WHERE-clause on IS NOT NULL are not needed.


P.S.
In your trigger query text you use the pattern for INNER JOIN
... FROM T1, T2, WHERE T1.pk = T2.fk ....
I like to suggest that you do not mix up INNER JOIN conditions with possible filter conditions in the WHERE-clause, but use
... FROM T1 INNER JOIN T2 ON T1.pk = T2.fk WHERE ....

Make sure that you create also UPDATE triggers on tCertificateSupplier and tParticipantProjectCertificate to prevent functional inconsistencies. Never use the argument; "this data will be maintained by an business-admin"; Murphy is somewhere out there!

Do not only ask the right question but be also complete and precise (use SQL expressions).
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by Sliderule »

For any other user ( exception dreamquartz ) with HSQL ( as of HSQL Version 2.X - NOT a Base Embedded HSQL - Version 1.8.0.10 ) as a database back-end, Triggers work perfectly.
dreamquartz wrote:After more testing, it turns out that the TRIGGER is not activated the moment the database is opened.
This seems strange, because as far as my knowledge goes, a TRIGGER is read, just like everything else, because it is part of of the "database.script"-file, when a database is opened.

I have to physically make a mistake once, before the TRIGGER fires, but not before that.
I wonder, since I know the database being used is "proprietary" ( as stated previously by user dreamquartz ) . . . it is likely if the test being performed was done with either RAW INSERT statements, OR, via an added Base record from the VIEW, rather than from a FORM, likely the real error will be discovered.

Since, user dreamquartz has asked me not to post anything . . . I will follow that request . . . but . . . suffice it to say, for any other user, HSQL Triggers work as documented, even within the examples given in the testrun HSQL directory.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

eremmel wrote:With the specification given above you are not complete:
1. CertificateSupplierAbbreviation-CertificateCode: UNIQUE
So we will not talk about CertificateSupplierShown: irrelevant. But what values can CertificateSupplierAbbreviation have?
i. some text
ii. empty string
iii. NULL
iV. Derived NULL value because tProjectInfo.FKCertificateSupplierID IS NULL

Is there an uniqueness on CertificateSupplierAbbreviation or is there a unique constraint on CertificateSupplierAbbreviation with NULL values excluded?
Which of the four above (i, ii, iii, iv) in combination of CertificateCode has to be validated for unique?
3. No CertificateSupplierShown if no CertificateSupplierAbbreviation
What does this mean?
a. tProjectInfo.FKCertificateSupplierID IS NULL
b. tProjectInfo"."FKCertificateSupplierID IS NOT NULL AND tCertificateSupplier.CertificateSupplierAbbreviation IS NULL
c. tProjectInfo"."FKCertificateSupplierID IS NOT NULL AND tCertificateSupplier.CertificateSupplierAbbreviation = ''
4. Both CertificateSupplierShown and CertificateSupplierAbbreviation can be empty
What does this mean?
d. tCertificateSupplier.CertificateSupplierAbbreviation = ''
(i)The c.-entry, however, is entered without a CertificateSupplier, and therefore must NOT cause the TRIGGER to fire.
(ii)The d.-entry is entered into the tParticipantProjectCertificate table without any CertificateSupplier and CertificateCode (both are NULL).
(i)This implies that you can change the "LEFT JOIN" into an "INNER JOIN" the result of this change is that you only consider defined relations between tCertificateSupplier and tParticipantProjectCertificate.
(ii) You are joining on "NewEntry"."CertificateCode" = "Existing"."CertificateCode", this condition is not satisfied when any of the two IS NULL.
So both conditions in the WHERE-clause on IS NOT NULL are not needed.


P.S.
In your trigger query text you use the pattern for INNER JOIN
... FROM T1, T2, WHERE T1.pk = T2.fk ....
I like to suggest that you do not mix up INNER JOIN conditions with possible filter conditions in the WHERE-clause, but use
... FROM T1 INNER JOIN T2 ON T1.pk = T2.fk WHERE ....

Make sure that you create also UPDATE triggers on tCertificateSupplier and tParticipantProjectCertificate to prevent functional inconsistencies. Never use the argument; "this data will be maintained by an business-admin"; Murphy is somewhere out there!

Do not only ask the right question but be also complete and precise (use SQL expressions).
Hi eremmel,
Thanks for your response.
For your reference, see also the attached sample database, where tCertificateSupplier is filled with samples, reflecting the real table.

The table tCertificateSupplier is a static table, and used in the database as a ListBox. To make sure no one is making changes to the table, someone (with knowledge and understanding) was appointed the task to maintain this table.

tCertificateSupplier.CertificateSupplierAbbreviation is either filled with some text (most likely a 2 or 3 letter abbreviation of the CertificateSuplier) or NULL.
The stated criteria describe the criteria surrounding CertificateSupplierAbbreviation.

Please be also aware of what the table tParticipantProjectCertificate can hold. The information includes a reference to a Person (FKPersonID). A Person can be placed in the table as long that there is a reference to Project info (FKProjectInfoID).

The tProjectInfo table holds information about a Project, including a possible reference to tCertificateSupplier.
The reference (FKCertificateSupplierID) can be as follows:
1. There is a FKCertificateSupplierID, referencing a CertificateSupplier with an Abbreviation (making the CertificateCodes entered in tParticipantProjectCertificate UNIQUE)
2. There is NO FKCertificateSupplierID. Therefore tProjectInfo.FKCertificateSupplierID is NULL.

The table tParticipantProjectCertificate can hold the following combinations:
a. FKProjectInfoID (either 1. or 2. type entry from above), FKPersonID, and CertificateCode
- If the "a.-1." combination is entered; UNIQUE
- if "a.-2." combination is entered; NOT UNIQUE
b. FKProjectInfoID, FKPersonID and NO CertificateCode; NOT UNIQUE

There are a lot of entries of FKProjectInfoID and FKPersonID combinations in tParticipantProjectCertificate, who do NOT have a CertificateSupplier, but DO have a CertificateCode. These entries are filtered out in the TRIGGER via

Code: Select all

"Existing"."CertificateSupplierAbbreviation" IS NOT NULL
. They are considered NOT UNIQUE

There are also a lot of entries of FKProjectInfoID and FKPersonID combinations in tParticipantProjectCertificate, who do NOT have a CertificateSupplier and a CertificateCode.
These entries are filtered out in the TRIGGER via

Code: Select all

"Existing"."CertificateCode" IS NOT NULL
. They are considered NOT UNIQUE.

Answer to 3. & 4.
tCertificateSupplier.CertificateSupplier can exist, therefore CertificateSupplierID NOT NULL and CertificateSupplierAbbreviation IS NULL and CertificateSupplierShown IS NULL

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

Thanks for telling the whole story again. Unfortunate your response is not addressing in detail all the questions.
tCertificateSupplier.CertificateSupplierAbbreviation is either filled with some text (most likely a 2 or 3 letter abbreviation of the CertificateSuplier) or NULL.
1. There is a FKCertificateSupplierID, referencing a CertificateSupplier with an Abbreviation (making the CertificateCodes entered in tParticipantProjectCertificate UNIQUE)
...
There are a lot of entries of FKProjectInfoID and FKPersonID combinations in tParticipantProjectCertificate, who do NOT have a CertificateSupplier, but DO have a CertificateCode. These entries are filtered out in the TRIGGER via
. They are considered NOT UNIQUE
The combination of these statements is still ambiguous:
The check on "Existing"."CertificateSupplierAbbreviation" IS NOT NULL can find its cause in:
A. a tripple with tCertificateSupplier.CertificateSupplierAbbreviation IS NULL, but that has to be unique.
B. a tuple with tProjectInfo.FKCertificateSupplierID IS NULL, is considered non-unique
You can solve than by changing the two LEFT JOINs into INNER JOINs.

It look like you still do not understand that the check
"Existing"."CertificateSupplierAbbreviation" IS NOT NULL
in the WHERE-clause adds nothing because it is impossible that "Existing"."CertificateSupplierAbbreviation" IS NULL due to the COALESCE() AND the outcome of the specified join relation on "NewEntry" and "Existing"

The same reasoning applies for "Existing"."CertificateCode" IS NOT NULL.

Last proposal:

Code: Select all

             SELECT 1
             FROM  (
                   SELECT
                      COALESCE("tCertificateSupplier"."CertificateSupplierAbbreviation",'') "CertificateSupplierAbbreviation",
                      NEWROW."CertificateCode" "CertificateCode"
                   FROM
                      "tProjectInfo"
                         INNER JOIN "tCertificateSupplier"
                            ON "tProjectInfo"."FKCertificateSupplierID" = "tCertificateSupplier"."CertificateSupplierID"
                   WHERE
                      "tProjectInfo"."ProjectInfoID" = NEWROW."FKProjectInfoID"
                      -- Not strict needed, but will reduce the test quickly to an empty result set.
                      AND NEWROW."CertificateCode" IS NOT NULL
                ) "NewEntry"
                INNER JOIN (
                   SELECT
                      COALESCE ( "tCertificateSupplier"."CertificateSupplierAbbreviation", '' ) "CertificateSupplierAbbreviation",
                      "tParticipantProjectCertificate"."CertificateCode" "CertificateCode"
                   FROM
                      "tParticipantProjectCertificate" 
                         INNER JOIN "tProjectInfo"
                            ON "tParticipantProjectCertificate"."FKProjectInfoID" = "tProjectInfo"."ProjectInfoID"
                         INNER JOIN "tCertificateSupplier"
                            ON "tProjectInfo"."FKCertificateSupplierID" = "tCertificateSupplier"."CertificateSupplierID"
                ) "Existing"
                   ON
                      "NewEntry"."CertificateCode" = "Existing"."CertificateCode"
                      AND "NewEntry"."CertificateSupplierAbbreviation" = "Existing"."CertificateSupplierAbbreviation"
Hora Est!
Last edited by eremmel on Wed Apr 05, 2017 6:58 pm, edited 1 time in total.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

Hi All,

Thanks to input from eremmel, the TRIGGER could be simplified.

The link https://www.mediafire.com/?87ezvydxww1wlan provides the latest version of the SampleBase.
I have expanded it there where necessary to explain what is required, and removed that was is not essential.
It incorporates the latest modified TRIGGER.

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
							 "tCertificateSupplier"."CertificateSupplierAbbreviation" "CertificateSupplierAbbreviation",
							 NEWROW."CertificateCode" "CertificateCode"
						 FROM
							 "tParticipantProjectCertificate"
								 INNER JOIN "tProjectInfo"
									 ON "tParticipantProjectCertificate"."FKProjectInfoID" = "tProjectInfo"."ProjectInfoID"
								 INNER JOIN "tCertificateSupplier"
									 ON "tProjectInfo"."FKCertificateSupplierID" = "tCertificateSupplier"."CertificateSupplierID"           
						 WHERE
							 "tProjectInfo"."ProjectInfoID" = NEWROW."FKProjectInfoID"
					 ) "NewEntry"
					 INNER JOIN
					 (
						 SELECT
							 COALESCE ( "tCertificateSupplier"."CertificateSupplierAbbreviation", '' ) "CertificateSupplierAbbreviation",
							 "tParticipantProjectCertificate"."CertificateCode" "CertificateCode"
						 FROM
							 "tParticipantProjectCertificate"
								 INNER JOIN "tProjectInfo"
									 ON "tParticipantProjectCertificate"."FKProjectInfoID" = "tProjectInfo"."ProjectInfoID"
								 INNER JOIN "tCertificateSupplier"
									 ON "tProjectInfo"."FKCertificateSupplierID" = "tCertificateSupplier"."CertificateSupplierID"
					 ) "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
It appears that so far everything is working according to set Criteria.
The Form fProjectCertificate allows entries as required.
The Query qOverview states what needs to be shown.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

If you are happy with it your own SQL knowledge.., but based on code review at least one glitch still exists in you current trigger.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

eremmel wrote:If you are happy with it your own SQL knowledge.., but based on code review at least one glitch still exists in you current trigger.
Hi eremmel,
intriguing.....
May I know what you mean?

I could not create any issues with entries.
It could be that you indicate a potential, based on an entry that will/might not occur.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

Compare the differences between your code and my last code: esp query "NewEntry".
DreamsPuzzle.png
DreamsPuzzle.png (7.72 KiB) Viewed 10795 times
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

eremmel wrote:Compare the differences between your code and my last code: esp query "NewEntry".
DreamsPuzzle.png
Uhm....
When I look at my SampleBase I submitted via MediaFire, where:
FKProjectInfo: 19
DateOfIssue: 04-05-2017
ParticipantProjectCertificate: 188
Person: Blanchard, Salvador
My CertificateCode shows: 2134561 and not PUD 231qwa, as DreamPuzzle.png shows.

Where did you find that?

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

The example is not that handy. Better to use the following:
1. Create new project (e.g. 20) and link it to PUD
2. Add via your form tProjectCertificate a person to project 20 and use CertificateCode '231qwa' and save it (it is possible)
3. Add via your form tProjectCertificate another person to project 20 and use CertificateCode '233qwa' and save it (it is not possible)
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

eremmel wrote:The example is not that handy. Better to use the following:
1. Create new project (e.g. 20) and link it to PUD
2. Add via your form tProjectCertificate a person to project 20 and use CertificateCode '231qwa' and save it (it is possible)
3. Add via your form tProjectCertificate another person to project 20 and use CertificateCode '233qwa' and save it (it is not possible)
I can reproduce this one.
You might have caught that what I indicated as a bug, above (see: viewtopic.php?f=13&t=86906&start=30#p413390).
I found that same behavior.
Am looking into it right now.

Question: Could that "bug" be caused by the fact that NEWROW is NULL when the TRIGGER is fired for the FIRST time?
You added the statement:

Code: Select all

NEWROW."CertificateCode" IS NOT NULL
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by eremmel »

dreamquartz wrote: Question: Could that "bug" be caused by the fact that NEWROW is NULL when the TRIGGER is fired for the FIRST time?
You added the statement:

Code: Select all

NEWROW."CertificateCode" IS NOT NULL
Dream
No. NEWROW is a table (with one row) reference and can never bet NULL of NOT NULL because it is not a field. The check on NEWROW."CertificateCode" IS NOT NULL is just a optimization because when CertificateCode IS NULL, the trigger should not be executed. no check is needed, so making the result set empty makes the trigger execution as economic as possible.

You broke your own trigger code by not taken over my code with care.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

Am still testing the TRIGGER. Status @ this point: No problems. Will keep you informed.
05/12/2017; TESTING COMPLETE, BUT STILL ONGOING. Consider issue solved.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply