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

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

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

Post by dreamquartz »

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

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
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

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

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

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 356 times
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
robleyd
Moderator
Posts: 5037
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Catching the PK of record checked against in TRIGGER

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

@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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
robleyd
Moderator
Posts: 5037
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Catching the PK of record checked against in TRIGGER

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

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).
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

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?
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

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

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

The MERGE INTO and TRIGGER type BEFORE INSERT was actually suggested by Fred Toussi.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

Any reference available? Your questions are at the level of HSQLDB, so ask support in HSQLDB forum.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Catching the PK of record checked against in TRIGGER

Post by dreamquartz »

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

CREATE TABLE t (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, description VARCHAR(100), matched BOOLEAN DEFAULT FALSE)

Code: Select all

INSERT INTO t VALUES (1, 'dining table', FALSE), (2, 'deck chair', FALSE)

Code: Select all

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

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Catching the PK of record checked against in TRIGGER

Post by eremmel »

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Post Reply