I am struggling to make sure all persons entered in a database are really unique.
The reason for this that records are to be linked to the right person in the database.
Having an 'other' entry will therefore not link data to the correct person.
It is required that all historical data, potentially going back over decades, is linked to the right and therefore same person.
I use a trigger 'before insert'
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)"
)
)
)
THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT = 'There appears to be a similar Person already recorded. Please verify the Existing entry!';
END IF;
END
There are potential, historical, errors in the database, due to missing information, or typos.
Missing information could be a NickName.
I have added a Boolean called 'Check' column added to the record, so that I can now update/modify an existing person, as a result of new personal information coming to light, or as a result of that typo.
The trigger 'before update'
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"."Surname" = NEWROW."Surname" AND
"tPerson"."GivenName" = NEWROW."GivenName"
) OR
(
"tPerson"."GivenName" = NEWROW."GivenName" AND
"tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
)
) 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
The reason for this trigger is that by changing information, there could be now a match with an existing record, that was not there before.
The User needs to verify the information, and make a decision. The decision could be YES indeed it is the same person, so the 'latest' must be removed, and all the links that are relevant must be rerouted to the initial entry, or NO the 'update is correct, and the Person is indeed unique.
The 'Check' can be set, so the 'before update' trigger allows the changes to proceed, and the 'check' can be removed.
This setup works in most cases, but NOT when the following is the case:
There is a person recorded with the same Surname, GivenName, but with a different DOB.
You could argue that you should set that Boolean 'check' for the existing record to prevent the 'before update' trigger to fire, but it might not be known to the User that there a similar person in the database.
You could also say, make the 'before update' trigger not look at that, but the verifications used in the 'before update' trigger are already as minimal as possible, so I do not really want to change much.
I also do not want to add a second Boolean 'check' because there is no way of telling how many similar persons are to be entered.
As a NOTE: All the Boolean 'Checks' in different tables are removed so that no related table does have any 'checks' set for a new entry, using
Code: Select all
'Remove all ""Check"
Sub Main(e)
Const cResetPersonCheck = "UPDATE ""tPerson"" SET ""Check""=False"
Const cResetPhoneFaxCheck = "UPDATE ""tPhoneFax"" SET ""Check""=False"
Const cResetEmailAddressCheck = "UPDATE ""tEmailAddress"" SET ""Check""=False"
Const cResetAddressBareCheck = "UPDATE ""tAddressBare"" SET ""Check""=False"
oActiveConnection = e.Source.Model.Parent.ActiveConnection
oPrepPersonCheck = oActiveConnection.prepareStatement(cResetPersonCheck)
n = oPrepPersonCheck.executeUpdate()
oPrepPhoneFaxCheck = oActiveConnection.prepareStatement(cResetPhoneFaxCheck)
n = oPrepPhoneFaxCheck.executeUpdate()
oPrepEmailAddressCheck = oActiveConnection.prepareStatement(cResetEmailAddressCheck)
n = oPrepEmailAddressCheck.executeUpdate()
oPrepAddressBareCheck = oActiveConnection.prepareStatement(cResetAddressBareCheck)
n = oPrepAddressBareCheck.executeUpdate()
NextRecord 'Call Sub NextRecord
End Sub
Sub NextRecord
Dim oDocument As Object
Dim oDispatcher As Object
oDocument = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDispatcher.executeDispatch(oDocument, ".uno:NewRecord", "", 0, Array())
End sub
My questions are:
Can anyone indicate if they are dealing with the high level of uniqueness of personal information entries I am dealing with, and
How do they go about that?
I thought I could solve this with TRIGGER 'before insert' and 'before update', but am unable to figure out how to deal this, in the situation where it is unknown how many similar people are, going to be, entered.
Any suggestions/thoughts are really appreciated
Dream