[Solved] TRIGGER use with a VIEW to prevent duplicates

Discuss the database features

[Solved] TRIGGER use with a VIEW to prevent duplicates

Postby dreamquartz » Wed Jan 11, 2017 8:05 am

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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: Unique for more than 1 table

Postby Villeroy » Thu Jan 12, 2017 1:29 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unique for more than 1 table

Postby dreamquartz » Fri Jan 13, 2017 7:48 pm

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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: Unique for more than 1 table

Postby Villeroy » Sat Jan 14, 2017 2:34 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unique for more than 1 table

Postby dreamquartz » Sun Jan 15, 2017 7:40 pm

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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: Unique for more than 1 table

Postby Villeroy » Sun Jan 15, 2017 8:01 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unique for more than 1 table

Postby dreamquartz » Sun Jan 15, 2017 9:03 pm

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   Expand viewCollapse view
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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: Unique for more than 1 table

Postby eremmel » Wed Jan 18, 2017 3:27 pm

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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: Unique for more than 1 table

Postby dreamquartz » Wed Jan 18, 2017 9:32 pm

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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: Unique for more than 1 table

Postby dreamquartz » Sat Jan 28, 2017 11:14 pm

Using the above (modified) Query
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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/triggers-chapt.html#trc_instead_of_triggers, it appears that the TRIGGER is correct.

Can someone provide me with some guidance, please?

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: Unique for more than 1 table

Postby eremmel » Sun Jan 29, 2017 9:00 pm

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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: Unique for more than 1 table

Postby dreamquartz » Sun Jan 29, 2017 10:30 pm

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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby eremmel » Mon Jan 30, 2017 11:28 pm

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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby dreamquartz » Wed Feb 01, 2017 7:33 am

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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby eremmel » Wed Feb 01, 2017 10:34 am

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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby dreamquartz » Thu Feb 02, 2017 4:13 am

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 124 times
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby eremmel » Thu Feb 02, 2017 11:16 am

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

Code: Select all   Expand viewCollapse view
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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby dreamquartz » Fri Feb 03, 2017 7:48 am

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

Code: Select all   Expand viewCollapse view
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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby eremmel » Fri Feb 03, 2017 1:02 pm

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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby dreamquartz » Sat Feb 04, 2017 4:28 am

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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby dreamquartz » Thu Feb 09, 2017 11:07 pm

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

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby eremmel » Thu Feb 09, 2017 11:23 pm

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   Expand viewCollapse view
        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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby chrisb » Sat Feb 11, 2017 2:42 pm

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.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 197
Joined: Mon Jun 07, 2010 4:16 pm

Re: TRIGGER use with a VIEW to prevent duplicates

Postby dreamquartz » Sat Feb 11, 2017 6:35 pm

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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby chrisb » Sun Feb 12, 2017 2:48 am

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   Expand viewCollapse view
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.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 197
Joined: Mon Jun 07, 2010 4:16 pm

Re: TRIGGER use with a VIEW to prevent duplicates

Postby dreamquartz » Mon Feb 20, 2017 6:08 pm

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   Expand viewCollapse view
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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby dreamquartz » Tue Feb 21, 2017 6:14 pm

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 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 712
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby eremmel » Tue Feb 21, 2017 7:53 pm

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   Expand viewCollapse view
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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: TRIGGER use with a VIEW to prevent duplicates

Postby chrisb » Tue Feb 21, 2017 8:03 pm

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.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 197
Joined: Mon Jun 07, 2010 4:16 pm

Re: TRIGGER use with a VIEW to prevent duplicates

Postby chrisb » Wed Feb 22, 2017 3:59 pm

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   Expand viewCollapse view
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.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 197
Joined: Mon Jun 07, 2010 4:16 pm

Next

Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests