[Solved] TRIGGER use with a VIEW to prevent duplicates
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[Solved] TRIGGER use with a VIEW to prevent duplicates
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 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.
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 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.
Re: Unique for more than 1 table
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Unique for more than 1 table
To be honest, No.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.
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.
Re: Unique for more than 1 table
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Unique for more than 1 table
It turns out to be making data entry more complicated and cumbersome, when trying to use a separate table.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.
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.
Re: Unique for more than 1 table
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Unique for more than 1 table
The forms I use have in some cases more than 10 sub-forms, so complex is quite relative.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.
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"
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.
Re: Unique for more than 1 table
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.
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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Unique for more than 1 table
Thanks,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.
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.
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.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Unique for more than 1 table
Using the above (modified) Query to create a VIEW ("vUniqueCertificate"), I am trying to create a TRIGGER, to check against duplicate Cetificates.
I can enter the following TRIGGER:
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
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
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;
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.
Re: Unique for more than 1 table
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....
"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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Unique for more than 1 table
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....
Hoping this statement
(taken from the manual) would be applicable, but apparently not?With the use of INSTEAD OF triggers a read-only view can effectively become updatable or insertable-into.
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.
Re: TRIGGER use with a VIEW to prevent duplicates
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
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
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...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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: TRIGGER use with a VIEW to prevent duplicates
Form_Sub Form is the method of entry.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 toCheck 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...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.
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.
Re: TRIGGER use with a VIEW to prevent duplicates
Just insert a log-record into a log table inside each trigger so you can follow what it going on.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.
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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: TRIGGER use with a VIEW to prevent duplicates
Added a SampleBase for clarification.eremmel wrote:Just insert a log-record into a log table inside each trigger so you can follow what it going on.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.
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 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 225 times
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: TRIGGER use with a VIEW to prevent duplicates
Why not a BEFORE INSERT trigger on "tParticipantProjectCertificate" with a check like:
This idea costs you $10,- to pay to a child related charity organization.
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 ...
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: TRIGGER use with a VIEW to prevent duplicates
Can I replace that $10 with volunteering for the Red Cross this weekend?eremmel wrote:Why not a BEFORE INSERT trigger on "tParticipantProjectCertificate" with a check like:
This idea costs you $10,- to pay to a child related charity organization.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 ...
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.
Re: TRIGGER use with a VIEW to prevent duplicates
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?
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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: TRIGGER use with a VIEW to prevent duplicates
Your example is making sense.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?
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.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: TRIGGER use with a VIEW to prevent duplicates
Fiddled around with the codeeremmel wrote:Why not a BEFORE INSERT trigger on "tParticipantProjectCertificate" with a check like:
This idea costs you $10,- to pay to a child related charity organization.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 ...
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
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.
Re: TRIGGER use with a VIEW to prevent duplicates
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:
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.
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"
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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: TRIGGER use with a VIEW to prevent duplicates
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?
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
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: TRIGGER use with a VIEW to prevent duplicates
Indeed it does.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?
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.
Re: TRIGGER use with a VIEW to prevent duplicates
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.
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
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: TRIGGER use with a VIEW to prevent duplicates
Hi Chrisb,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
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.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: TRIGGER use with a VIEW to prevent duplicates
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.
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.
Re: TRIGGER use with a VIEW to prevent duplicates
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.
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.
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 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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: TRIGGER use with a VIEW to prevent duplicates
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.
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
Re: TRIGGER use with a VIEW to prevent duplicates
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.
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