[SOLVED]Catching the PK of record checked against in TRIGGER

Discuss the database features

[SOLVED]Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Tue Apr 17, 2018 12:02 am

Hello All,

Trying to deal with a time consuming issue still, but now trying to find an answer via different means.

Having a large View of many addresses (~10,000) that is recreated every single time a new Address has to be recorded, so it can be referenced for use.

I have a TRIGGER (with help of eremmel) that checks that part of an address that describes the location of the building, recorded in table tAddressBare.
The moment that the TRIGGER is fired and indicates that the Address already exists in tAddressBare, I would like to be able to catch the AddressBareID (Primary Key) of that specific record, so that I can use it to speed up the process.

Is there a way to "catch" and record an identified PK in a TRIGGER?

Dream

Update: 10/10/2018; started new thread.
Last edited by dreamquartz on Thu Oct 11, 2018 1:38 am, edited 1 time in total.
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 642
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Tue Apr 17, 2018 12:50 pm

You might report the key-value back in your error message or save the key value in a table that contain (the last) collision(s).
For the latter you might checkout: hsqldb-create-trigger-throws-exception-wrong-or-missing-data-impact-clause
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Wed Apr 18, 2018 8:09 pm

eremmel wrote:You might report the key-value back in your error message or save the key value in a table that contain (the last) collision(s).
For the latter you might checkout: hsqldb-create-trigger-throws-exception-wrong-or-missing-data-impact-clause


I checked the information, but come across a couple of things.
1. I do not want to show the PK, but use it later.
2. HSQLDB "BEFORE" TRIGGERS only work on one table in my experience. I would like to record the PK in such a way that I can use it later. The question is now, if I can only work with one table (in my case tAddressBare), where to store the PK or a reference to it?

The TRIGGER, see below, checks the Neighbourhoods against the City where they are in, because of the way people are entering address information.

Code: Select all   Expand viewCollapse view
CREATE TRIGGER PUBLIC.ADDRESS BEFORE INSERT ON PUBLIC."tAddressBare"
    REFERENCING NEW ROW AS NEWROW FOR EACH ROW
    BEGIN ATOMIC
       IF EXISTS
          (
             SELECT
                *
             FROM
                PUBLIC."tAddressBare"
                   INNER JOIN PUBLIC."tCity" "CityOrNeighbourhood"
                      ON "tAddressBare"."FKCityID" = "CityOrNeighbourhood"."CityID"
                   LEFT JOIN PUBLIC."tCity" "Neighbourhoods"
                      ON
                         (
                            "Neighbourhoods"."AreaID" = "CityOrNeighbourhood"."CityID" OR
                            "Neighbourhoods"."AreaID" = "CityOrNeighbourhood"."AreaID" OR
                            "Neighbourhoods"."CityID" = "CityOrNeighbourhood"."AreaID"
                         )
                   LEFT JOIN PUBLIC."tCity" "CitySynonym"
                      ON
                         "CitySynonym"."CityID" = "Neighbourhoods"."AreaID"
             WHERE
                "tAddressBare"."StreetNumber" = NEWROW."StreetNumber" AND
                "tAddressBare"."StreetName" = NEWROW."StreetName" AND
                "tAddressBare"."FKTypeOfStreetID" = NEWROW."FKTypeOfStreetID" AND
                (
                   "CityOrNeighbourhood"."CityID" = NEWROW."FKCityID" OR
                   "Neighbourhoods"."CityID" =  NEWROW."FKCityID" OR
                   "Neighbourhoods"."AreaID" =  NEWROW."FKCityID" OR
                   "CitySynonym"."CityID"   = NEWROW."FKCityID"
                 )
          )
          THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='The same Address already exists. Please verify the Existing entry!';
       END IF;
    END


Any suggestions are appreciated,

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

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Thu Apr 19, 2018 8:19 am

To save a collision in an other table, you have to do a successful insert and therefor a commit. So you can not raise an error to the end user in that case.
In the before trigger you are allowed to change the data of the inserted row, so you might flag that this row is invalid and insert in the after trigger the collision row.

A possible solution is to use a Java based before trigger and veto the insert, signal via a 'message queue' an other java thread that inserts the violated row into an event table.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Thu Apr 19, 2018 6:52 pm

That's a very interesting line of thought. By adding a column Boolean to the table, and mark it the moment the collision has been identified. Later the Boolean can be reset for re-use.
I will pursue that.

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

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Sun Apr 22, 2018 11:00 pm

Looking for an answer to insert in an exiting record.
I am missing something.

Using a simple example to test function.
Added a Boolean "Check" to the table "tFilterProject" for use in the TRIGGER.

This is what I tried so far:
1. INSERT INTO "tFilterProject" ("FilterProjectID", "FKAddressBareID", "Check") VALUES (0, 1, 1)
2. INSERT INTO "tFilterProject" ("FilterProjectID", "FKAddressBareID", "Check") VALUES (IDENTITY(), 2, 1)
3. INSERT INTO "tFilterProject" ("FilterProjectID", "FKAddressBareID", "Check") VALUES (IDENTITY(), 3, TRUE)
4. INSERT INTO "tFilterProject" ("FKAddressBareID", "Check") VALUES (3, TRUE) WHERE "FilterProjectID = 0


No. 1-3 works if "FilterProjectID" is NOT a Primary Key.
I want to be able to do something like No. 4. It gives an error of course.

Is there a way to do something like this or is this the wrong approach?
Do I have to delete the record where "FilterProjectID"= 0 every time and reset the the numbering to start with '0'?

Dream
INSERT INTO.odb
(3.64 KiB) Downloaded 212 times
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 642
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Postby robleyd » Mon Apr 23, 2018 2:45 am

I haven't looked at your sample - but if you want to change an existing record would you not normally use an UPDATE rather than an INSERT? Or did I miss something?

Pseudo-SQL:
UPDATE table SET field1=newvalue [,field2=newvalue2 ...] WHERE otherfield=value
Cheers
David
Apache OpenOffice 4.1.6 RC1 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2162
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Mon Apr 23, 2018 8:54 am

@robleyd, this question has a long history. It is about a complex validation of an insert performed in a trigger and how to know why that insert is/should be considered invalid. It is far beyond the obvious stuff. You are encouraged to break your brains over it and give a smart solution.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Catching the PK of record checked against in TRIGGER

Postby robleyd » Mon Apr 23, 2018 10:24 am

Ah - in that case it may be just a bit above my pay grade so I'll opt not to fracture my cerebellum :lol: I need to keep as much of it functional as I can.
Cheers
David
Apache OpenOffice 4.1.6 RC1 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2162
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Mon May 07, 2018 1:09 am

The line of thought about INSERT was not a bad idea.
It appears not to work.
I am still trying to figure out what to do.

Dream,

P.S. It makes sense if the function exists in the first place.
Will communicate with HSQLDB.org to request it as an option.
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 642
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Mon May 07, 2018 9:03 am

dreamquartz wrote:Looking for an answer to insert in an exiting record.
I am missing something.


You miss the fundamentals of SQL you can only update an existing record. So utilize the insert in the BEFORE TRIGGER and use the AFTER TRIGGER to do additional steps. Read over and over again the HSQLDB documentation so you get a clear understanding of what is allowed and possible with triggers.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Mon May 07, 2018 8:20 pm

Thanks eremmel,

My quest is to identify which record caused the TRIGGER to fire.
I am indeed pursuing your route, and one that was offered by Fred Toussi, being: MERGE in combination with USING.

I do have to say that the HSQLDB documentation is not really of much help so far.

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

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Mon May 07, 2018 9:41 pm

I used the same documentation to develop the triggers to support you so far.
The before trigger that used to raise the error contains the query that gives you the collision row. In the AFTER trigger you can still use the NEWROW (same as in before trigger).
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Tue May 15, 2018 1:54 pm

I worked out my own suggestion and added a table to collect the collisions. That collision table is completely maintained by triggers on tAddressBare.
Examine the tables, views etc.
Checkout the queries.

The database document is a Base document with external database. Allow the macro's to run to let it use the external database:SampleBase2.zip

P.S.
What will be your donation to the Red Cross organization?
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Tue May 15, 2018 4:41 pm

Not having checked your example, I donate my time as a volunteer to the ICRC. I am active, when able, in different areas of the organization.

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

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Wed May 23, 2018 1:23 am

Being again very busy with other things, I now had some time to look at your suggestions.
The principles you are indicating are looking great, but I think it is more than I am looking for.
If you look at the TRIGGER example, the moment it is fired, and there is a collision, the message will be shown.
At that point the TRIGGER has been made aware of a recordid.
I only want to mark/record that recordid so that the user does not have to be presented with a whole list of addresses. By using the collision recordid, the User is now presented with one record.
So, instead of the TRIGGER just informing the User that there was a collision, I want the TRIGGER also to store that specific recordid, and therefore populate a listbox with one record to select from.

I am trying to figure out if your suggestions are letting me create something like this.

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

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Wed May 23, 2018 10:00 am

When you show a message to the user, it is due to an error, this implies an abort of transaction, so no way to store the collision record.
With the tools I presented to you you might add a sub-form to your address entry form that will populate the collision record (or stay empty when all is OK).
When you want more you have to program a Java trigger. You can return an error and fire an event to another thread (and transactions) that stores the collision data.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Wed Jun 13, 2018 10:28 pm

OK,

Trying to follow the principle of "MERGE INTO".

This provides an option to INSERT and UPDATE with the TRIGGER type " BEFORE INSERT".

Apparently missing something again.

Any help is appreciated.
Code: Select all   Expand viewCollapse view
CREATE TRIGGER PUBLIC.MAIL BEFORE INSERT ON PUBLIC."tMail"
    REFERENCING NEW ROW AS NEWROW FOR EACH ROW
    BEGIN ATOMIC      
MERGE INTO “tMail” USING (VALUES(IDENTITY(), NEWROW."Mail") AS vals(x,y) ON “tMail”.”MailID” = vals.x
WHEN MATCHED
         THEN
             UPDATE SET “tMail”.”Matched”  = TRUE         
       WHEN NOT MATCHED
         THEN
             INSERT VALUES (IDENTITY(), NEWROW."Mail")
END;


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

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Thu Jun 14, 2018 8:16 am

You missed the documentation about triggers:
BEFORE Triggers

A trigger that is declared as BEFORE DELETE cannot modify the deleted row. In other words, it cannot decide to delete a different row by changing the column values of the row. A trigger that is declared as BEFORE INSERT and BEFORE UPDATE can modify the values that are inserted into the database. For example, a badly formatted string can be cleaned up by a trigger before INSERT or UPDATE.

BEFORE triggers cannot modify the other tables of the database. All BEFORE triggers can veto the action by throwing an exception.

Because BEFORE triggers can modify the inserted or updated rows, all constraint checks are performed after the execution of the BEFORE triggers. The checks include NOT NULL constraints, length of strings, CHECK constraints, and FOREIGN key constraints.

Also read the help of the other two trigger types, the phrasing makes clear what the limitation is of the BEFORE trigger.
Modifying values is something different than executing a Merge statement.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Thu Jun 14, 2018 7:51 pm

The MERGE INTO and TRIGGER type BEFORE INSERT was actually suggested by Fred Toussi.
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 642
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Thu Jun 14, 2018 8:18 pm

Any reference available? Your questions are at the level of HSQLDB, so ask support in HSQLDB forum.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Catching the PK of record checked against in TRIGGER

Postby dreamquartz » Sun Jun 17, 2018 8:27 pm

The following creates, fills, and merges into table t.
The RESULT is as expected.

The "MERGE INTO TABLE t" part I should be able to put in a TRIGGER type 'BEFORE INSERT'.

Code: Select all   Expand viewCollapse view
CREATE TABLE t (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, description VARCHAR(100), matched BOOLEAN DEFAULT FALSE)

Code: Select all   Expand viewCollapse view
INSERT INTO t VALUES (1, 'dining table', FALSE), (2, 'deck chair', FALSE)

Code: Select all   Expand viewCollapse view
MERGE INTO t USING
    (
       VALUES
         (1, 'conference table', FALSE),
          (14, 'sofa', FALSE),
          (5, 'coffee table', FALSE)
   ) AS vals(id,description,matched)
       ON t.id = vals.id
    WHEN MATCHED
       THEN UPDATE SET t.matched = TRUE
    WHEN NOT MATCHED
       THEN INSERT VALUES vals.id, vals.description, vals.matched

RESULT
dining table 1 TRUE
deck chair 2 FALSE
coffee table 5 FALSE
sofa 14 FALSE

TRIGGER BEFORE INSERT, based on the SQL, above:
Code: Select all   Expand viewCollapse view
CREATE TRIGGER PUBLIC.MAIL BEFORE INSERT ON PUBLIC."tMail"
    REFERENCING NEW ROW AS NEWROW FOR EACH ROW
    BEGIN ATOMIC      
MERGE INTO "tMail" USING
    (
       VALUES
         (
             IDENTITY(),
             NEWROW."FKTypeOfEmailPhoneFaxID",
             NEWROW."FKPersonID",
             NEWROW."FKEmployeeID",
             NEWROW."Mail",
             NEWROW."Check"
          )
    ) AS vals(u,v,w,x,y,z)
       ON NEWROW."Mail" = vals.y
WHEN MATCHED
         THEN
             UPDATE SET "tMail"."Check"  = TRUE         
       WHEN NOT MATCHED
         THEN
             INSERT VALUES vals.u, vals.v, vals.w, vals.x, vals.y, vals.z;
END


RESULT:
1: wrong or missing data impact clause in declaration: MODIFIES SQL


Any thoughts?

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

Re: Catching the PK of record checked against in TRIGGER

Postby eremmel » Tue Jun 19, 2018 9:21 am

dreamquartz/john doe,
It would have been correct to link to your post at CREATE TRIGGER BEFORE INSERT Result Set iso quoting from this discussion according your understanding. Based on the contents of that thread I do not see any reason to change my opinion. My suggestion is that you post your trigger code in that thread and see what the master himself answers on your attempt.
As I stated before, your question is a pure HSQLDB related thing and has not to do with AOO/LO.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands


Return to Base

Who is online

Users browsing this forum: No registered users and 6 guests