HAPPY NEW YEAR!!!!
Does anyone ever encounter a TRIGGER NOT to fire where it is known that it should?
Sometimes, and not that often, I notice that a TRIGGER does not fire, when it supposes to.
I recently came across a Person who I wanted to enter, and my TRIGGER did not respond to the following:
Recorded Person: Jones, Jack; 11/14/1984
New entry: Jones, Tim, Jackson; 11/14/1984
It is known it is the same Person
As far as I know, the TRIGGER does verify against Surname-DOB
There are multiple TRIGGERS in the Database for Address, PhoneNumber, EmailAddress and more.....
Code: Select all
CREATE TRIGGER PUBLIC.PERSON BEFORE INSERT ON PUBLIC."tPerson"
REFERENCING NEW ROW AS NEWROW FOR EACH ROW
BEGIN ATOMIC
IF EXISTS
(
SELECT
"Person"."Surname",
"Person"."GivenName",
"Person"."Nickname",
"Person"."MiddleInitial(s)",
"Person"."DateOfBirth",
"Person"."Check",
"PersonWithNickname"."Surname",
"PersonWithNickname"."GivenName",
"PersonWithNickname"."Nickname",
"PersonWithNickname"."MiddleInitial(s)",
"PersonWithNickname"."DateOfBirth",
"PersonWithNickname"."Check"
FROM
PUBLIC."tPerson" "Person"
LEFT JOIN PUBLIC."tPerson" "PersonWithNickname" ON
(
"PersonWithNickname"."Nickname"="Person"."GivenName" OR
"PersonWithNickname"."Nickname"="Person"."Nickname"
)
WHERE
(
"Person"."Surname" = NEWROW."Surname" AND
(
"Person"."DateOfBirth" = NEWROW."DateOfBirth" OR
"Person"."GivenName" = NEWROW."GivenName" OR
"Person"."MiddleInitial(s)" = NEWROW."MiddleInitial(s)" OR
"PersonWithNickname"."Nickname" = NEWROW."GivenName" OR
"Person"."GivenName" = NEWROW."Nickname" OR
"Person"."Nickname" = NEWROW."Nickname"
) AND
"Person"."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
Dream
PLEASE STAY SAFE!!!!