[Solved] Intermitted/rare TRIGGER issues

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

[Solved] Intermitted/rare TRIGGER issues

Post by dreamquartz »

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

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

Re: Intermitted/rare TRIGGER issues

Post by dreamquartz »

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

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply