@karolus & @Villeroy,
It appears to be related to a TRIGGER we added a while back.
The TRIGGER is helping the User to make sure that the correct Person is updated.
Due to the fact that the User is dealing with a lot of people with the same GivenName-Surname, and/or potential other combinations (see TRIGGER), the User wanted to make sure that the 'correct' Person is updated.
The simplest solution was to add a type of TRIGGER BEFORE UPDATE (see below).
Apparently this TRIGGER 'interferes' with the said Macro.
Removing this TRIGGER and all is 'well' concerning running the Macro.
However, the Macro is used to remove any "Check" which was set by the User in the first place, because the issues, raised by any TRIGGER for "Check" in the tables "tPerson", "tEmailAddress", "tAddressBare", and "tAddress".
By setting "Check" to 'TRUE', the User is able to insert and/or update any of the records in either of the aforementioned tables.
In a sense, setting "Check" to 'TRUE' the table related TRIGGER(s) are now 'fooled' and will not fire if an identical record is to be inserted (think PhoneNumber for people living in the same house) or a record is to be updated (think typo in DOB).
However, leaving the "Check" = 'TRUE' in place will potentially cause 'issues' later on, when a record is updated 'again' that has "Check" = 'TRUE' still.
Therefore, after the User has updated a record, the User can push a button to run the Macro, and so remove all "Checks" that were set in the past.
One of the steps we ask the User to take is to push the button for removing the set "Checks" once a while, just like making them aware that 'checkpoint defrag' and 'backups' are important. A lot of the forms the User has access to have macro programmed buttons for the last 2.
The principle works great for the tables "tEmailAddress", "tAddressBare", and "tAddressBare".
There are TRIGGERS 'monitoring' all of the aforementioned tables, but they are of the type TRIGGER BEFORE INSERT.
The only table that is also 'monitored' by a TRIGGER BEFORE UPDATE is "tPerson".
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
Just for clarification I also added the TRGGER BEFORE INSERT for table "tPerson".
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
I do not know how to solve this issue for the table "tPerson", where both new records and updating existing records MUST be monitored.
In summary, this is the real issue that was triggered by a Macro.
Therefore the title is updated.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.