[Solved] Intermitted/rare TRIGGER issues

Discuss the database features

[Solved] Intermitted/rare TRIGGER issues

Postby dreamquartz » Wed Jan 06, 2021 6:05 pm

Hi All,

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   Expand viewCollapse view
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


Any suggestion is appreciated,

Dream

PLEASE STAY SAFE!!!!
Last edited by dreamquartz on Sat Feb 27, 2021 8:09 pm, edited 1 time in total.
LO 6.x, HSQLDB 2.6.0 & Ubuntu 20.04 LTS.
dreamquartz
 
Posts: 813
Joined: Mon May 30, 2011 4:02 am

Re: Intermitted/rare TRIGGER issues

Postby dreamquartz » Sat Feb 27, 2021 8:08 pm

dreamquartz wrote:Hi All,

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   Expand viewCollapse view
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


Any suggestion is appreciated,

Dream

PLEASE STAY SAFE!!!!

I think I found the answer to my problems.
I mostly have 'BEFORE INSERT' TRIGGERS, because most of the data is new.
I added for some situations now also 'BEFORE UPDATE' TRIGGERS.

It appears at this point in time that that does the trick.

Dream
LO 6.x, HSQLDB 2.6.0 & Ubuntu 20.04 LTS.
dreamquartz
 
Posts: 813
Joined: Mon May 30, 2011 4:02 am


Return to Base

Who is online

Users browsing this forum: No registered users and 8 guests