[CLOSED] TRIGGER BEFORE UPDATE for multiple records

[dreamquartz]

Hello All,

I am not able to solve the following situation, related to a TRIGGER called PERSON_BU
The table tPerson was filled in the past with 'old' data from a Spreadsheet.
At the time there were no TRIGGER BEFORE INSERT and TRIGGER BEFORE UPDATE introduced.

The User came accross the following situation:
There are now 2 Persons in the DataBase with the same Surname and DOB.
One Person was imported comes from the SpreadSheet.

The new Person was added to the table tPerson in the DataBase.

Sometime after the new Person was added, both TRIGGERS were introduced.
The reason for the TRIGGER BEFORE INSERT is to help the User to confirm that a Person in the DataBase only appears once.
The reason for the TRIGGER BEFORE UPDATE is to help the User to identify if the potential update of the records of a Person not lead to duplicate records of the same Person.

When information is entered in the DataBase, it is done by hand.
Mistakes are made, handwriting can be difficult to decipher.
Therefore a Person can indeed be in the DataBase more than once.
Because of the fact that a lot of persons do provide their personal information on a regular basis, it is possible that the latest information confirms a multi emtry of the same person.
The User has a process in place to update/merge the multiple records into one unique record.

The principle is simple.
By introducing a Boolean to the different tables, the User can simply confirm that this is the correct record that needs to be updated.
It is only for the table tPerson of utmost importance that the Person is UNIQUE.

Nowadays, there are multiple TRIGGERS in place to help the User to identify potential duplication.
Addresses, Surnames, GivenNames, and Nicknames can change.
However, PhoneNumbers and EmailAddresses likely way less frequent.
For all of these parts there are TRIGGERS in place.
Therefore the chance that there is duplication is minimized.

It turned out that of one Person the records needed to be updated in tPerson.

The TRIGGER BEFORE UPDATE PERSON_BU identifies that there is a record that is identical, based on Surname and DOB, and therefore fires.
The User can 'Check' the record, and whene there is just one entry in the table, the User can update and confirm the information, if needed by a second party, and remove the 'Check'.

However this TRIGGER does NOT work with duplicate information, in this case Surname and DOB.

I can not figure out how to solve the issue.

Can someone please provide me some guidance?

Kind regards,

Dream

Code: Select all

CREATE TRIGGER PUBLIC.PERSON_BU BEFORE UPDATE ON PUBLIC."tPerson"
	 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
	 BEGIN ATOMIC
		 IF EXISTS
			 (
				 SELECT
					 "tPerson"."Surname",
					 "tPerson"."GivenName",
					 "tPerson"."Nickname",
					 "tPerson"."MiddleInitial(s)",
					 "tPerson"."DateOfBirth"
				 FROM
					 PUBLIC."tPerson"
				 WHERE
					 (
						 (
							 "tPerson"."Surname" = NEWROW."Surname" AND
							 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
						 ) OR
						 (
							 "tPerson"."Nickname" = NEWROW."Nickname" AND
							 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
						 ) OR
						 (
							 "tPerson"."GivenName" = NEWROW."GivenName" AND
							 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
						 )
					 ) AND
					 "tPerson"."Check" = NEWROW."Check"
			 )
			 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT = 'There appears to be a similar Person already recorded. Please verify the Existing entry!';
		 END IF;
 END

Code: Select all

CREATE TRIGGER PUBLIC.PERSON_BI BEFORE INSERT ON PUBLIC."tPerson"
	 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
	 BEGIN ATOMIC
		 IF EXISTS
			 (
				 SELECT
					 "tPerson"."Surname",
					 "tPerson"."GivenName",
					 "tPerson"."Nickname",
					 "tPerson"."MiddleInitial(s)",
					 "tPerson"."DateOfBirth"
				 FROM
					 PUBLIC."tPerson"
				 WHERE
					 (
						 "tPerson"."Surname"=NEWROW."Surname" AND
						 (
							 "tPerson"."DateOfBirth"=NEWROW."DateOfBirth" OR
							 "tPerson"."GivenName"=NEWROW."GivenName" OR
							 "tPerson"."GivenName"=NEWROW."Nickname" OR
							 "tPerson"."Nickname"=NEWROW."GivenName" OR
							 "tPerson"."Nickname"=NEWROW."Nickname" OR
							 "tPerson"."MiddleInitial(s)"=NEWROW."MiddleInitial(s)"
						 ) AND
						 "tPerson"."Check" = FALSE
					 )
			 )
			 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='There appears to be a similar Person already recorded. Please verify the Existing entry!';
		END IF;
END

03/28/23 UPDATE
Closed and opened a new related thread

[eremmel]

What do you mean with "However this TRIGGER does NOT work with duplicate information, in this case Surname and DOB."?
1. Which of both given triggers?
2. 'duplicate information' means that there are already multiple records in database that are effective a duplicate, but are not recorded/marked as such?

Any way it is good to have some reports available that list suspected records that might indicate duplication. A administrator can then mitigate or correct the data.

[dreamquartz]

@eremmel

1st
The BEFORE INSERT does do its job without any problems, but that is with new entries.
If there are more records that potentially are duplicates, based on the criteria, the User can simply look them up and make a decision.

Our solution for dealing with the historical data that was imported, was to create the BEFORE UPDATE.
The moment that the User started to deal with personal information in the past, it was, and nowadays still is, handwritten data from registration forms.

It was created after the BEFORE INSERT, because of existing, imported data.

You can imagine that re-registrations of the same person, could/can lead to serious issues.
All the issues you can imagine do occur, varying from a simple typo (m or n) to GivenNames that are actually NickNames (Yi Xin vs Annie).
The GivenName issues come from persons who did not legally change there name, but do not want to be addressed in day to day life by their legal name.
That means that re-registrations can be quite interesting.......

ad 1.
BEFORE UPDATE

ad 2.
During the design process, the User requested that there be no duplicate information about Persons.
The 1st step of the solution was to filter through the original data (Spreadsheet) and manually update all that was needed.
That process took around 200 personhours, and very tedious.

The moment the information was imported, there was still a lot of manual labour needed to connect all the loose ends.

To comment on you last comment:
We wanted to keep it simple for the User and create the TRIGGER BEFORE INSERT and add a BOOLEAN called 'Check' to the table tPerson.
The moment the TRIGGER fires, the User can set 'Check' = TRUE, and enter the data.
However, leaving 'Check' = TRUE in place, does mean the record will not be part of a 'new' TRIGGER event.

Therefore it was decided to add a simple MACRO to set all 'Check' in all tables to 'NULL' by a simple push of a button.

The BEFORE UPDATE design followes the same principle, but when you look @

Code: Select all

"tPerson"."Check" = NEWROW."Check"

, it appears to create a problem for multiple duplications.
One 'Check' can be changed from NULL to TRUE, but recording the change, will lead BEFORE UPDATE to fire for any other record.
That means that an update cannot be pushed at all.

I understand it is a design issue, but cannot seem to figure out how to solve it.
I, again, want to keep it simple for the User.
I do not want to hard code an other 'Check' for something so similar to one record duplication, because before you know it there are potentially multiple duplications.

Dream

[eremmel]

Without understanding this all, I know that you can never compare NULL value via =. SQL Purists like to stay away from NULL.
For a simple check-field the values False and True should be fine, and I do not think you need NULL.
Do you need NULL than you should write your compare differently (i.e. extend it with a test on NULL for both fields).

[dreamquartz]

@eremmel
'Check' is nothing more than to prevent BEFORE INSERT and BEFORE UPDATE to fire.
It is completely manually set and can be manually removed or via the Macro,
Both TRIGGERS compare the status of the 'Check' with NEW ROW. If there is a difference, records can be inserted, or updated.
Nothing more.
The reason for resetting 'Check' is to make sure the User always does the same thing.
'Check' the record(s), INSERT or UPDATE and return to 'Normal'.

We probably are going to integrate the Macro into either a Macro for 'Checkpoint Defrag' (button operated) or as part of 'Shutdown Sequence'.

But by setting 'Check' = TRUE for use for a single record @ BEFORE UPDATE, now we can have conflicting statuses, because of the issue described above.

Dream

[eremmel]

Describe in detail for which combination of "tPerson"."Check" and NEWROW."Check" do you want to let the UPDATE trigger fail or succeed. Assume here that there exists possible multiple tPersons instances on any match. Use the possible values True, False, NULL
Do it not by words but by means of a table. You can then determine the logic you need in your trigger (I guess).

[dreamquartz]

@eremmel,

If you look at 'CREATE TRIGGER PUBLIC.PERSON_BU BEFORE UPDATE ON PUBLIC."tPerson"' above, you see the section 'WHERE' and there is the complete overview of combinations combined with tPerson.Check for which BEFORE UPDATE needs to fire.
This TRIGGER is based on example of 'CREATE TRIGGER trigone BEFORE INSERT ON customer' in 'guide.pdf' pg. 225 (239 of 275) HyperSQL version 2.6.1.

One of the things I would like to see is how the execution order is for the BEFORE UPDATE.
This helps me with analyzing the issue.

In a table format:
for existing date in tPerson
BEFORE UPDATE must NOT fire, due to Check = TRUE
see: picture 1-1
-----
BEFORE UPDATE MUST fire, due to Check = FALSE
see: picture 2-1
What my understanding of TRIGGERS is, that they work sequential for each row the 'come across'.
With other words when it comes across a TRIGGER event it fires, and it will not 'look further' to check if there is an other TRIGGER event.
This makes sense completely.....

Suppose the following situation:
see: picture 3-1
For PersonID=236, the User experienced a TRIGGER event, and changes Check=FALSE to Check=TRUE.
Staying on the record, the User can make a change by adding the NickName.
When the record is committed, BEFORE UPDATE will instantly fire for PersonID=11578, because tPerson.Surname=NEWROW.Surname, tPerson.DOB=NEWROW.DOB, and Check=FALSE.

It is even so, that after commit, PersonID=236 will always create a TRIGGER event for everything, because tPerson.Surname=NEWROW.Surname, tPerson.DOB=NEWROW.DOB, and tPerson.Check=NEWROW.Check


Dream

PS sorry about the attachment sizes. Could not make them fit and could not create a table via 'Table=' here.....

[eremmel]

I think that we are both completely disconnected with our language...
I resign!

[dreamquartz]

@eremmel
That's to bad.
I thought we had a connection here LOL
I tried my best to respond to your last post....

You know, it would be nice if there was better literature available about TRIGGERS.
I did not find anything that is useful so far.

Thanks anyway.

Dream