Catching the PK of record checked against in TRIGGER

Discuss the database features

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
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 630
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: 970
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: 630
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: 970
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: 630
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 10 times
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 630
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.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1585
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: 970
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.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1585
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: 630
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: 970
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: 630
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: 970
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: 970
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: 630
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: 630
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: 970
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 5 guests