[Solved] TRIGGER use with a VIEW to prevent duplicates

Discuss the database features
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

[Solved] TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

Hi All,

Have a question concerning Unique.

I have 3 tables (see attached).
The reason for this solution is that in a lot of cases the documents start with the same code (listed in tCertificateSupplier, as a reference in table tProjectInfo).
The table tProjectInfo holds the general information for a project.
The table tCertificateSuppier is only accessible as a ListBox.

For ease of entry, the form used for entering the required data has a Form_Sub-Form construction, where the CertificateSupplier is selected (or NOT), and in the Sub-Form the CertificateCodes are entered. Most CertificateCodes are Numeric, so the Numeric part of the Keyboard can be used efficiently. It does save a lot of time, and reduces errors significantly.

However, the moment a Code like "ABC 123456" needs to be entered, it MUST be unique.
The moment the CertificateSupplier is not entered, there is no need.

At this point, the Database holds more than 6000 entries, where the 3 tables are involved in one way or an other. It would be quite a challenge to re-write/re-design sections, so that is of the table.

I am looking at something like a Macro to be used as an Event for either "Before record change" or "After record change".

The question is can it be done, and if so, can someone direct me in the right direction.

Thanks in advance,

Dream
Diagram2.png
01/29/2017; Modified title to reflect topic better

03/28/2017; RE-OPENED because the TRIGGER will not activate when DataBase.script is read when opening the split database.
Last edited by dreamquartz on Sat May 13, 2017 2:20 am, edited 4 times in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unique for more than 1 table

Post by Villeroy »

This is a many-to-many relation, isn't it? Just put the 2 keys in a separate table with relations to either side.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Unique for more than 1 table

Post by dreamquartz »

Villeroy wrote:This is a many-to-many relation, isn't it? Just put the 2 keys in a separate table with relations to either side.
To be honest, No.
The tCertificateSupplier holds the departments where the Documents come from. This table is maintained by the QA department.
It is just a reference table, solely used as a listbox.
The table tProjectInfo stores all the general info related to a project, so there is no repeat entry needed.
The table tParticipantProjectCertificate holds the specific information for each document.

Hope this helps a little.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unique for more than 1 table

Post by Villeroy »

Nevertheless you may keep the two keys in a separate table where you can ensure uniqueness and link the other two tables by their repective keys.
A TRIGGER (HSQL2) may provide another solution to the problem but I know next to nothing about triggers.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Unique for more than 1 table

Post by dreamquartz »

Villeroy wrote:Nevertheless you may keep the two keys in a separate table where you can ensure uniqueness and link the other two tables by their repective keys.
A TRIGGER (HSQL2) may provide another solution to the problem but I know next to nothing about triggers.
It turns out to be making data entry more complicated and cumbersome, when trying to use a separate table.
I am hoping to have some kind of test to check the status, prior to saving.
I know not that much about creating a macro of some sort, so I am looking for some guidance.

By the way; I do not know anything about TRGGERS.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unique for more than 1 table

Post by Villeroy »

Welll, the test would be an index on both fields in a separated table. It may add some complexity to your form layout, though.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Unique for more than 1 table

Post by dreamquartz »

Villeroy wrote:Welll, the test would be an index on both fields in a separated table. It may add some complexity to your form layout, though.
The forms I use have in some cases more than 10 sub-forms, so complex is quite relative.

I am able to create a View of the tables

Code: Select all

SELECT "tParticipantProjectCertificate"."ParticipantProjectCertificateID", "tCertificateSupplier"."CertificateSupplierAbbreviation", "tParticipantProjectCertificate"."CertificateCode" FROM { oj "tProjectInfo" LEFT OUTER JOIN "tCertificateSupplier" ON "tProjectInfo"."FKCertificateSupplierID" = "tCertificateSupplier"."CertificateSupplierID" }, "tParticipantProjectCertificate" WHERE "tParticipantProjectCertificate"."FKProjectInfoID" = "tProjectInfo"."ProjectInfoID"
This is a unique table.

Issues for now:
1. This table needs to be updated, the moment an entry has taken place.
2. How to test against it?

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

Re: Unique for more than 1 table

Post by eremmel »

Based on the relationships and info you gave it looks like that an entry in tCertificateSupplier might have an CertificateSupplierAbbreviation or not (NULL). So why not adding to tParticipantProjectCertificate an LU (lookup) relation to tCertificateSupplier.CertificateSupplierAbbreviation and add an unique index on tParticipantProjectCertificate.{ LU_CertificateSupplierAbbreviation, CertificateCode}. So model it as tCertificateSupplier:tParticipantProjectCertificate == *:1

Not nice, but might be effective.

P.S.
Looking at the projects you are working on it might get time to learn macro programming. I guess you end up with more and more changes to your data models with each iterations and because you can not easily regenerate forms when you make fundamental changes to your data model in a next iteration, the only way will be the escape route through macro's and/or triggers.
But this will route will end always in a horror scenario, because you try to write 'professional' applications without any professional tooling... This is called penny wise pound foolish.
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: Unique for more than 1 table

Post by dreamquartz »

eremmel wrote:Based on the relationships and info you gave it looks like that an entry in tCertificateSupplier might have an CertificateSupplierAbbreviation or not (NULL). So why not adding to tParticipantProjectCertificate an LU (lookup) relation to tCertificateSupplier.CertificateSupplierAbbreviation and add an unique index on tParticipantProjectCertificate.{ LU_CertificateSupplierAbbreviation, CertificateCode}. So model it as tCertificateSupplier:tParticipantProjectCertificate == *:1

Not nice, but might be effective.

P.S.
Looking at the projects you are working on it might get time to learn macro programming. I guess you end up with more and more changes to your data models with each iterations and because you can not easily regenerate forms when you make fundamental changes to your data model in a next iteration, the only way will be the escape route through macro's and/or triggers.
But this will route will end always in a horror scenario, because you try to write 'professional' applications without any professional tooling... This is called penny wise pound foolish.
Thanks,

Not a bad idea.
I will pursue this.

I realize that indeed.
I am working both Triggers and Macros as we write.
There is an unique index available to the CertificateSupplier_CertificateCode. See mentioned code above. It is created via a Query, so a View is created.

Dream
PS: Concerning Macros however. I, just like Villeroy, think they are evil. :evil:

UPDATE: 01/20/2017; Not quite following what you meant.
Thought I understood.

It should be similar to an Event like: "Before record change".
Checking against the content of even a view (vCertificate), where multiple tables are brought together with a unique Identifier.

Trying to wrap my head around Triggers, turns out to be hassle. Reading everything I can find about Triggers, does not provide me with a solution so far.

I am looking for more ideas around Triggers, if someone can give some guidance.

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

Re: Unique for more than 1 table

Post by dreamquartz »

Using the above (modified) Query

Code: Select all

SELECT COALESCE ( "tCertificateSupplier"."CertificateSupplierShown" || ' ', '' ) || "tParticipantProjectCertificate"."CertificateCode" "Certificate", "tCertificateSupplier"."CertificateSupplierID", "tProjectInfo"."ProjectInfoID", "tParticipantProjectCertificate"."ParticipantProjectCertificateID" FROM { oj "tProjectInfo" LEFT OUTER JOIN "tCertificateSupplier" ON "tProjectInfo"."FKCertificateSupplierID" = "tCertificateSupplier"."CertificateSupplierID" }, "tParticipantProjectCertificate" WHERE "tParticipantProjectCertificate"."FKProjectInfoID" = "tProjectInfo"."ProjectInfoID" AND "tParticipantProjectCertificate"."CertificateCode" IS NOT NULL AND "tCertificateSupplier"."CertificateSupplierAbbreviation" IS NOT NULL
to create a VIEW ("vUniqueCertificate"), I am trying to create a TRIGGER, to check against duplicate Cetificates.

I can enter the following TRIGGER:

Code: Select all

CREATE TRIGGER CERTIFICATE_CHECK
INSTEAD OF INSERT
	ON "vUniqueCertificate" 
	REFERENCING NEW ROW AS NEWROW
	FOR EACH ROW
	BEGIN ATOMIC
		IF EXISTS (SELECT * FROM "vUniqueCertificate" WHERE "vUniqueCertificate"."Certificate" = NEWROW."Certificate") THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This Certificate already exists, please enter a different Certificate!';
		END IF;
	END;
However I get the following:
I can still enter a duplicate Certificate, but according to: http://hsqldb.org/doc/2.0/guide/trigger ... f_triggers, it appears that the TRIGGER is correct.

Can someone provide me with some guidance, please?

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

Re: Unique for more than 1 table

Post by eremmel »

Documentation states:
"All BEFORE triggers can veto the action by throwing an exception"
This remark is only at BEFORE triggers, you are using ÍNSTEAD OF INSERT 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: Unique for more than 1 table

Post by dreamquartz »

eremmel wrote:Documentation states:
"All BEFORE triggers can veto the action by throwing an exception"
This remark is only at BEFORE triggers, you are using ÍNSTEAD OF INSERT trigger....
:crazy:
Hoping this statement
With the use of INSTEAD OF triggers a read-only view can effectively become updatable or insertable-into.
(taken from the manual) would be applicable, but apparently not?
I have created a VIEW, and am hoping to use that to verify against.

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 »

What about delegation. The INSTEAD OF works on the view and issues an insert the insert is blocked due to BEFORE trigger. Try such a chain of command with some simple tests (set specific field values so you know the flow etc.).

Just another thought when reading your post. How are you inserting the data? Are you using the view or the plain table? You were referring to
A trigger that is declared on a VIEW, is an INSTEAD OF trigger. This term means when an INSERT, UPDATE or DELETE statement is executed with the view as the target, the trigger action is all that is performed, and no further data change takes place on the view. The trigger action can include all the statements that are necessary to change the data in the tables that underlie the view, or even other tables, such as audit tables. With the use of INSTEAD OF triggers a read-only view can effectively become updatable or insertable-into.
Check the bold phrases to see if they match what you did. You got data into the tables but that could not via your view, because your query should address the view and your trigger should do the insert(s), but there is no insert. It comes to very close reading...
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:What about delegation. The INSTEAD OF works on the view and issues an insert the insert is blocked due to BEFORE trigger. Try such a chain of command with some simple tests (set specific field values so you know the flow etc.).

Just another thought when reading your post. How are you inserting the data? Are you using the view or the plain table? You were referring to
A trigger that is declared on a VIEW, is an INSTEAD OF trigger. This term means when an INSERT, UPDATE or DELETE statement is executed with the view as the target, the trigger action is all that is performed, and no further data change takes place on the view. The trigger action can include all the statements that are necessary to change the data in the tables that underlie the view, or even other tables, such as audit tables. With the use of INSTEAD OF triggers a read-only view can effectively become updatable or insertable-into.
Check the bold phrases to see if they match what you did. You got data into the tables but that could not via your view, because your query should address the view and your trigger should do the insert(s), but there is no insert. It comes to very close reading...
Form_Sub Form is the method of entry.
Entering date 1st in tProjectInfo (Form), and then into tParticipantProjectCertificate (Sub_Form).
This prevents re-entry and mistakes.

The VIEW is just a separate VIEW for reference purposes.
The above described QUERY is used to create it.
It uses the tables in which the data is entered.

The VIEW is only created out of convenience, because it is more difficult to test against the separate tables individually.

I am working on very simplified DataBases to figure out what the "problem(s)" are.
I am not sure how to show a RESULT SET of a test, done by a TRIGEER, so it is trial and error for now.

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 »

I am not sure how to show a RESULT SET of a test, done by a TRIGEER, so it is trial and error for now.
Just insert a log-record into a log table inside each trigger so you can follow what it going on.

You are using the tables in form to insert data. This implies direct inserts on those two tables. It is important to understand the execution flow that happens when something happens in a database. All objects in a database like tables, views and indexes etc are passive: they do not act on their own. They are used by the database engine when they are referenced from a user action like a query. Example:
1 So you execute an insert query with some objects (e.g. tables, fields).
2 The database parses the query and takes those objects in consideration (fields, tables, indexes of those tables, constraints on those tables/fields, triggers on those tables) and makes a plan in with order to traverse all those elements
3 During executions all those elements are visited and the proper action on them is taken.
4 When all actions on those elements complete successful the statement completes successful

So when you do define a trigger on a view that is not part of your insert statement, the trigger will be not part of the execution plan. So you should move your validation to another object that is part of the elements involved in your insert statement(s).

P.S.
You are now working for some time with Base and consulting this forum. Your projects are complex and tend to have a professional level (in respect to complexity). To be able to operate efficiently it is very important to have a clear understanding of fundamental concepts and practices in software design and implementation concepts of e.g. an RDBMS. An old, but still good book is "Fundamentals of Database Systems" of Elmasri et al. My version misses the discussion of Object to Relational mapping issues, but you can learn that from a good book about Hibernate.
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:
I am not sure how to show a RESULT SET of a test, done by a TRIGEER, so it is trial and error for now.
Just insert a log-record into a log table inside each trigger so you can follow what it going on.

You are using the tables in form to insert data. This implies direct inserts on those two tables. It is important to understand the execution flow that happens when something happens in a database. All objects in a database like tables, views and indexes etc are passive: they do not act on their own. They are used by the database engine when they are referenced from a user action like a query. Example:
1 So you execute an insert query with some objects (e.g. tables, fields).
2 The database parses the query and takes those objects in consideration (fields, tables, indexes of those tables, constraints on those tables/fields, triggers on those tables) and makes a plan in with order to traverse all those elements
3 During executions all those elements are visited and the proper action on them is taken.
4 When all actions on those elements complete successful the statement completes successful

So when you do define a trigger on a view that is not part of your insert statement, the trigger will be not part of the execution plan. So you should move your validation to another object that is part of the elements involved in your insert statement(s).

P.S.
You are now working for some time with Base and consulting this forum. Your projects are complex and tend to have a professional level (in respect to complexity). To be able to operate efficiently it is very important to have a clear understanding of fundamental concepts and practices in software design and implementation concepts of e.g. an RDBMS. An old, but still good book is "Fundamentals of Database Systems" of Elmasri et al. My version misses the discussion of Object to Relational mapping issues, but you can learn that from a good book about Hibernate.
Added a SampleBase for clarification.
It is, however, an embedded version, to keep the size down.

When running Query1, it is showing that duplication is possible (see 'HRD 45632'). This is and must be an unique document that can never be re-issued under the same code.
I am trying to prevent that.

Dream
Attachments
SampleBase.odb
(13.9 KiB) Downloaded 218 times
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 »

Why not a BEFORE INSERT trigger on "tParticipantProjectCertificate" with a check like:

Code: Select all

IF EXISTS (
	SELECT 1 
	FROM (  SELECT COALESCE(cs.CertificateSupplierShown,'') AS "cs_css", NEWROW.CertificateCode
		FROM tProjectInfo pi 
		LEFT JOIN tCertificateSupplier cs ON pi.FKCertificateSupplierID = cs.CertificateSupplierID
		WHERE pi.ProjectInfoID = NEWROW.FKProjectInfoID
	) X
	INNER JOIN (SELECT COALESCE(cs.CertificateSupplierShown,'') AS "cs_css", ppc.CertificateCode
		FROM tParticipantProjectCertificate ppc
		INNER JOIN tProjectInfo pi ON ppc.FKProjectInfoID = pi.ProjectInfoID
		LEFT JOIN tCertificateSupplier cs ON pi.FKCertificateSupplierID = cs.CertificateSupplierID
	) Y
	ON X.CertificateCode = Y.CertificateCode
		AND X.cs_css = Y.cs_css
) THEN ...
This idea costs you $10,- to pay to a child related charity organization.
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:Why not a BEFORE INSERT trigger on "tParticipantProjectCertificate" with a check like:

Code: Select all

IF EXISTS (
	SELECT 1 
	FROM (  SELECT COALESCE(cs.CertificateSupplierShown,'') AS "cs_css", NEWROW.CertificateCode
		FROM tProjectInfo pi 
		LEFT JOIN tCertificateSupplier cs ON pi.FKCertificateSupplierID = cs.CertificateSupplierID
		WHERE pi.ProjectInfoID = NEWROW.FKProjectInfoID
	) X
	INNER JOIN (SELECT COALESCE(cs.CertificateSupplierShown,'') AS "cs_css", ppc.CertificateCode
		FROM tParticipantProjectCertificate ppc
		INNER JOIN tProjectInfo pi ON ppc.FKProjectInfoID = pi.ProjectInfoID
		LEFT JOIN tCertificateSupplier cs ON pi.FKCertificateSupplierID = cs.CertificateSupplierID
	) Y
	ON X.CertificateCode = Y.CertificateCode
		AND X.cs_css = Y.cs_css
) THEN ...
This idea costs you $10,- to pay to a child related charity organization.
Can I replace that $10 with volunteering for the Red Cross this weekend?
Did not know that all of this is possible with a TRIGGER.
Will check it out.

Thank you (Dank je wel),

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 »

Hi Dream

Red Cross is also a good target to support.
The query is just a quick idea. Normally the complexity of SQL is in respect to where you can place something not an issue. You can do wonderful things with triggers and at the end of the day no one will understand all the relations and you get nice deadlocks etc. I've seen applications that quickly extended business logic via trigger: that is the nightmare. Your kind of constraint checking is fine.
Do you have Dutch background?
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:Hi Dream

Red Cross is also a good target to support.
The query is just a quick idea. Normally the complexity of SQL is in respect to where you can place something not an issue. You can do wonderful things with triggers and at the end of the day no one will understand all the relations and you get nice deadlocks etc. I've seen applications that quickly extended business logic via trigger: that is the nightmare. Your kind of constraint checking is fine.
Do you have Dutch background?
Your example is making sense.
I have never come across a solution like that for a TRIGGER.

I do get around a lot.
I picked that up.

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 »

eremmel wrote:Why not a BEFORE INSERT trigger on "tParticipantProjectCertificate" with a check like:

Code: Select all

IF EXISTS (
	SELECT 1 
	FROM (  SELECT COALESCE(cs.CertificateSupplierShown,'') AS "cs_css", NEWROW.CertificateCode
		FROM tProjectInfo pi 
		LEFT JOIN tCertificateSupplier cs ON pi.FKCertificateSupplierID = cs.CertificateSupplierID
		WHERE pi.ProjectInfoID = NEWROW.FKProjectInfoID
	) X
	INNER JOIN (SELECT COALESCE(cs.CertificateSupplierShown,'') AS "cs_css", ppc.CertificateCode
		FROM tParticipantProjectCertificate ppc
		INNER JOIN tProjectInfo pi ON ppc.FKProjectInfoID = pi.ProjectInfoID
		LEFT JOIN tCertificateSupplier cs ON pi.FKCertificateSupplierID = cs.CertificateSupplierID
	) Y
	ON X.CertificateCode = Y.CertificateCode
		AND X.cs_css = Y.cs_css
) THEN ...
This idea costs you $10,- to pay to a child related charity organization.
Fiddled around with the code

Code: Select all

CREATE TRIGGER CERTIFICATE_CHECK BEFORE INSERT ON "tParticipantProjectCertificate"
    REFERENCING NEW ROW AS NEWROW FOR EACH ROW
   BEGIN ATOMIC
    IF EXISTS (
       SELECT 1
       FROM ( SELECT COALESCE("cs"."CertificateSupplierShown",'') AS "cs_css", NEWROW."CertificateCode"
          FROM "tProjectInfo" "pi"
          LEFT JOIN "tCertificateSupplier" "cs" ON "pi"."FKCertificateSupplierID" = "cs"."CertificateSupplierID"
          WHERE "pi"."ProjectInfoID" = NEWROW."FKProjectInfoID"
       ) "X"
       INNER JOIN (SELECT COALESCE("cs"."CertificateSupplierShown",'') AS "cs_css", "ppc"."CertificateCode"
          FROM "tParticipantProjectCertificate" "ppc"
          INNER JOIN "tProjectInfo" "pi" ON "ppc"."FKProjectInfoID" = "pi"."ProjectInfoID"
          LEFT JOIN "tCertificateSupplier" "cs" ON "pi"."FKCertificateSupplierID" = "cs"."CertificateSupplierID"
       ) "Y"
       ON "X"."CertificateCode" = "Y"."CertificateCode"
          AND "X"."cs_css" = "Y"."cs_css"
    ) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This Certificate already exists, please enter a different Certificate!';
      END IF;
   END
The error I get so far is: "1: user lacks privilege or object not found: X.CertificateCode"
I also left it to its full extend without i.e. 'AS "cs_css"', but same results.

Reading upon more information, does not help so far either.

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 »

It might be that you are not allowed to address NEWROW.CertificateCode in the select clause.
Try to define the trigger without referencing CertificateCode, so try something like:

Code: Select all

        SELECT 1
       FROM ( SELECT COALESCE("cs"."CertificateSupplierShown",'') AS "cs_css"
          FROM "tProjectInfo" "pi"
          LEFT JOIN "tCertificateSupplier" "cs" ON "pi"."FKCertificateSupplierID" = "cs"."CertificateSupplierID"
          WHERE "pi"."ProjectInfoID" = NEWROW."FKProjectInfoID"
       ) "X"
       INNER JOIN (SELECT COALESCE("cs"."CertificateSupplierShown",'') AS "cs_css"
          FROM "tParticipantProjectCertificate" "ppc"
          INNER JOIN "tProjectInfo" "pi" ON "ppc"."FKProjectInfoID" = "pi"."ProjectInfoID"
          LEFT JOIN "tCertificateSupplier" "cs" ON "pi"."FKCertificateSupplierID" = "cs"."CertificateSupplierID"
       ) "Y"
       ON "X"."cs_css" = "Y"."cs_css"
Or
Try to test your SQL statement as a query and replace the NEWROW.field with literals.
Or
Investige if you can assign a local variable in the trigger to store the values you need of NEWROW and try to use them in the query.
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 have a question,

does the field 'CertificateSupplierID.CertificateSupplierAbbreviation' contain only one entry per Supplier?
in other words does each unique supplier have only one 'CertificateSupplierID.CertificateSupplierAbbreviation' the value of which is static?
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
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

chrisb wrote:dreamquartz i have a question,

does the field 'CertificateSupplierID.CertificateSupplierAbbreviation' contain only one entry per Supplier?
in other words does each unique supplier have only one 'CertificateSupplierID.CertificateSupplierAbbreviation' the value of which is static?
Indeed it does.

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.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: TRIGGER use with a VIEW to prevent duplicates

Post by chrisb »

i feel a little apprehensive because i may have misinterperated the content (yet again) but if the following statement is true

if
"tProjectInfo.FKCertificateSupplierID" is not a required value i.e. may be null
then when
"tProjectInfo.FKCertificateSupplierID" is not null i.e. does contain a value
then
we must ascertain that NEWROW."tParticipantProjectCertificate.CertificateCode" has not previously been issued to "tCertificateSupplier.CertificateSupplierID"
where
"tCertificateSupplier.CertificateSupplierID" = "tProjectInfo.FKCertificateSupplierID".

in a nutshell if "tProjectInfo.FKCertificateSupplierID" is not null then ensure that "CertificateCode" has not previously been issued to said "tCertificateSupplier.CertificateSupplierID".

then the code below may resolve your issue.

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 "FKProjectInfoID", "CertificateCode" FROM "tParticipantProjectCertificate") A
	JOIN
	(SELECT "ProjectInfoID", "FKCertificateSupplierID" FROM "tProjectInfo") B
	ON B."ProjectInfoID" = A."FKProjectInfoID"
	WHERE "CertificateCode" = NEWROW."CertificateCode"
	AND "FKCertificateSupplierID" IN
	(
		SELECT "FKCertificateSupplierID" FROM 
		(SELECT "FKProjectInfoID" FROM "tParticipantProjectCertificate" WHERE "FKProjectInfoID" = NEWROW."FKProjectInfoID") A
		JOIN
		(SELECT "ProjectInfoID", "FKCertificateSupplierID" FROM "tProjectInfo") B ON B."ProjectInfoID" = A."FKProjectInfoID"
	)
)
	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
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Post by dreamquartz »

chrisb wrote:i feel a little apprehensive because i may have misinterperated the content (yet again) but if the following statement is true

if
"tProjectInfo.FKCertificateSupplierID" is not a required value i.e. may be null
then when
"tProjectInfo.FKCertificateSupplierID" is not null i.e. does contain a value
then
we must ascertain that NEWROW."tParticipantProjectCertificate.CertificateCode" has not previously been issued to "tCertificateSupplier.CertificateSupplierID"
where
"tCertificateSupplier.CertificateSupplierID" = "tProjectInfo.FKCertificateSupplierID".

in a nutshell if "tProjectInfo.FKCertificateSupplierID" is not null then ensure that "CertificateCode" has not previously been issued to said "tCertificateSupplier.CertificateSupplierID".

then the code below may resolve your issue.

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 "FKProjectInfoID", "CertificateCode" FROM "tParticipantProjectCertificate") A
	JOIN
	(SELECT "ProjectInfoID", "FKCertificateSupplierID" FROM "tProjectInfo") B
	ON B."ProjectInfoID" = A."FKProjectInfoID"
	WHERE "CertificateCode" = NEWROW."CertificateCode"
	AND "FKCertificateSupplierID" IN
	(
		SELECT "FKCertificateSupplierID" FROM 
		(SELECT "FKProjectInfoID" FROM "tParticipantProjectCertificate" WHERE "FKProjectInfoID" = NEWROW."FKProjectInfoID") A
		JOIN
		(SELECT "ProjectInfoID", "FKCertificateSupplierID" FROM "tProjectInfo") B ON B."ProjectInfoID" = A."FKProjectInfoID"
	)
)
	THEN
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CERTIFICATE ALREADY EXISTS';
		END IF;
	END
Hi Chrisb,

Sorry for the late response.

Your code seems to be working in the example I provided.
I will check it further and will be checking it against a full load DataBase.

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 »

To Chrisb,

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.

I am working with the TRIGGER to find out if I can sort it out, but probably still need guidance, because of the apparent complexity of this issue.

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 »

He John doe...
It looks like that you can use NEWROW in the WHERE-clause, but not in the SELECT-clause, but after testing I found a solution.
 Edit: The query is updates after some testing... 

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("cs"."CertificateSupplierShown",'') AS "cs_css", NEWROW."CertificateCode" AS "CertificateCode"
          FROM "tProjectInfo" "pi" 
          LEFT JOIN "tCertificateSupplier" "cs" ON "pi"."FKCertificateSupplierID" = "cs"."CertificateSupplierID" 
	  WHERE "pi"."ProjectInfoID" = NEWROW."FKProjectInfoID"
       ) "X"
       INNER JOIN (SELECT COALESCE("cs"."CertificateSupplierShown",'') AS "cs_css", "ppc"."CertificateCode"
          FROM "tParticipantProjectCertificate" "ppc"
          INNER JOIN "tProjectInfo" "pi" ON "ppc"."FKProjectInfoID" = "pi"."ProjectInfoID"
          LEFT JOIN "tCertificateSupplier" "cs" ON "pi"."FKCertificateSupplierID" = "cs"."CertificateSupplierID"
       ) "Y"
       ON "X"."CertificateCode" = "Y"."CertificateCode"
          AND "X"."cs_css" = "Y"."cs_css"
  ) THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='This Certificate already exists, please enter a different Certificate!';END IF;END
The X-branch constructs the relation of the new inserted row (NEWROW) from tProjectInfo to tCertificateSupplier
The Y-branch constructs any relation from tParticipantProjectCertificate to tCertificateSupplier via tProjectInfo
With the INNER JOIN on Y and X we try to identify a match between those two sets X and Y. When that is the case we have a duplicate.

Note:.
The only technical issue with my first post was that we needed an alias specification to NEWROW."CertificateCode".
BTW I'm confused about the unique constraint. Is with 'Shown' of 'Abbreviated'? In the above conversation you switch.
Last edited by eremmel on Tue Feb 21, 2017 10:59 pm, edited 4 times 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)
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 see that eremmel has posted a reply.

this post is in response to the questions raised in your previous post.

my previous post was so long winded in an effort to clarify this very point.

as the value of "tCertificateSupplier.CertificateSupplierShown" is static its content is insignificant.

remember that "tProjectInfo.FKCertificateSupplierID" is the eqivalent of "tCertificateSupplier.CertificateSupplierID".

what matters to us is the value of "tProjectInfo.FKCertificateSupplierID".
if it's null then no checks are required.
if it's not null that we need to assertain the value of "tParticipantProjectCertificate.CertificateCode" has not previously been assigned to the 'CertificateSupplier'.
that's what my code does.
the "CertificateCode" can be issued an infinite number of times but not more than once per individual supplier.

i have not downloaded your example but have tested the trigger & believe it to be fit for purpose.
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
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've had a look at my original code & although it seems to work ok i'm not particularly happy with it.
i guess that eremmels code works fine & is likely to be the better option.
however i'm posting the code below in order to demonstrate that the value of "tCertificateSupplier.CertificateSupplierShown" is non compulsory in effecting a solution.

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
Post Reply