Solution Two: Using A Database Trigger |
This second solution is a little more complex and involves writing a Trigger for your database. This is an example of a trigger written for an Oracle database using the Oracle PL/SQL language (this code was compiled cleanly and tested on Oracle Database 9.2 using WinSQL to send the query). The trigger is executed when an A04 Registration insert/update needs to be done on the database. We want to verify that the PRK/MRN combination in the message matches the stored values in the PID table. The data checking results decide if a new record should be inserted or an existing record updated or simply an error reported.
Three tables exist for this example - REGISTRATION_TABLE_TEMP, REGISTRATION_TABLE and PID. The data is sent to the REGISTRATION_TABLE_TEMP. The conditions are checked and the appropriate insert/updates are made to REGISTRATION_TABLE and PID (if any). We may add code to periodically clean the table REGISTRATION_TABLE_TEMP so that it remains small.
/*
Logic:
A04 - Registration
Is there a PRK and an MRN (PRK = internal id, MRN = external id)
IF not, add the record
IF so, do they match the existing PRK and MRN in the table "PID"?
IF not, print exception "PRK / MRN mismatch"
IF match, update the record.
Assume:
Schema: HENRY
Tables: REGISTRATION_TABLE_TEMP, REGISTRATION_TABLE and PID
(and REGISTRATION_TABLE has attributes PRK, MRN, ATT1, ATT2 picked up from the incoming message)
Eg. test Insert statement: INSERT INTO HENRY.REGISTRATION_TABLE_TEMP VALUES('446', '123', 'ATT777', 'ATT111');
*/
CREATE TRIGGER TRIGGER_CHECK_AO4_REGISTRATION
BEFORE INSERT OR UPDATE ON REGISTRATION_TABLE
REFERENCING NEW AS newRow OLD AS oldRow
FOR EACH ROW
DECLARE
NUM_RECORDS NUMBER;
NUM_RECORDS2 NUMBER;
ERROR_MSG VARCHAR(200);
ERR_CODE NUMBER;
ONLY_UPDATE EXCEPTION;
BEGIN
ERROR_MSG := 'No error message';
/* check for the presence of the PRK and MRN fields */
SELECT COUNT(*) INTO NUM_RECORDS FROM HENRY.REGISTRATION_TABLE
WHERE HENRY.REGISTRATION_TABLE.PRK = :newRow.PRK AND HENRY.REGISTRATION_TABLE.MRN = :newRow.MRN;
IF( NUM_RECORDS = 0) THEN
/* this PRK, MRN combination is not there - add the record */
INSERT INTO HENRY.REGISTRATION_TABLE VALUES(:newRow.PRK, :newRow.MRN, :newRow.ATT1, :newRow.ATT2);
INSERT INTO HENRY.PID VALUES(:newRow.PRK, :newRow.MRN);
ELSE
/* The PRK, MRN values are there - now check if they match the existing values in table PID.
If the values do not match, raise the NO_DATA_FOUND exception */
SELECT COUNT(*) INTO NUM_RECORDS2 FROM HENRY.PID
WHERE HENRY.PID.PRK = :newRow.PRK AND HENRY.PID.MRN = :newRow.MRN;
IF(NUM_RECORDS2 = 0) THEN
RAISE NO_DATA_FOUND; /* report a PRK/MRN mismatch error */
ELSE
/* values match - update the record */
UPDATE HENRY.REGISTRATION_TABLE
SET HENRY.REGISTRATION_TABLE.PRK = :newRow.PRK,
HENRY.REGISTRATION_TABLE.MRN = :newRow.MRN,
HENRY.REGISTRATION_TABLE.ATT1 = :newRow.ATT1,
HENRY.REGISTRATION_TABLE.ATT2 = :newRow.ATT2
WHERE HENRY.REGISTRATION_TABLE.PRK = :newRow.PRK AND HENRY.REGISTRATION_TABLE.MRN = :newRow.MRN;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERROR_MSG := 'PRK / MRN mismatch - combination not found in PID table';
WHEN TOO_MANY_ROWS THEN
ERROR_MSG := 'More than one entry found for this PRK/MRN combination';
WHEN OTHERS THEN /* Catch-all exception handling */
ERROR_MSG := SUBSTR(SQLERRM,1,200);
ERR_CODE := SQLCODE;
END TRIGGER_CHECK_AO4_REGISTRATION;
.
run;
/* print any error messages for debugging */
PRINT :ERROR_MSG;
|