Hi all,
I was handed the code you see here to make a trigger out of it. We started to write a Storproc for the requirement but found out we do not have licennse for the C compiler needed for the storeproc on the Mainframe.
Simply stated, the purpose of the trigger is to determine county and payee of a given transaction item that it receives before allowing insert into the payment table.
Problem is, Triggers doen not allow IF statements and I will very much like to retain the original code if I can but I need to rework the code to eliminate the IF statements....
Can a SQL/Trigger guru "in the house" lend me a hand here? The code follows and thanks:
--This trigger determines the payee and county for any payment created in the system.
CREATE TRIGGER DB2ADMIN.PAYMENT_I NO CASCADE
BEFORE INSERT ON DB2ADMIN.PAYMENT
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE V_TABLE_ERROR VARCHAR(20) DEFAULT 'UNK';
DECLARE V_FL_FFP CHAR(1);
DECLARE V_FL_OVERRIDE_PRNT CHAR(1);
DECLARE V_FL_OVERRIDE_PRNT_SRVC CHAR(1);
DECLARE V_ID_BSNS INTEGER;
DECLARE V_CD_COUNTY_CNT INTEGER;
DECLARE V_CD_CNTY INTEGER;
SET V_TABLE_ERROR = 'ASSIGNMENT';
SET V_CD_COUNTY_CNT =
( SELECT COUNT(c.CD_COUNTY)
FROM ASSIGNMENT A, ORG_LOCATION_UNIT B,
ORG_LOCATION c
WHERE A.ID_GRP_LVL1 = NEW.ID_CASE
AND A.CD_ASGN_CTGRY = 1
AND A.CD_ASGN_ROLE = 1
AND A.FL_OPEN = 'Y'
AND A.ID_UNIT = B.ID_UNIT
AND B.ID_LOCATION = C.ID_LOCATION);
IF (V_CD_COUNTY_CNT = 1)
THEN
SET V_CD_CNTY =
(SELECT C.CD_COUNTY
FROM ASSIGNMENT A, ORG_LOCATION_UNIT B, ORG_LOCATION c
WHERE A.ID_GRP_LVL1 = NEW.ID_CASE
AND A.CD_ASGN_CTGRY = 1 AND A.CD_ASGN_ROLE = 1
AND A.FL_OPEN = 'Y'
AND A.ID_UNIT = B.ID_UNIT AND
B.ID_LOCATION = C.ID_LOCATION);
ELSE
SET V_TABLE_ERROR = 'CASE_MASTER';
SET V_CD_CNTY = (SELECT CD_RGN FROM CASE_MASTER
WHERE ID_CASE = NEW.ID_CASE);
END IF;
SET NEW.CD_CNTY = V_CD_CNTY;
SET V_TABLE_ERROR = 'PROVIDER_ORG';
SET V_FL_FFP =(SELECT FL_NFP FROM PROVIDER_ORG
WHERE ID_PRVD_ORG = NEW.ID_BSNS);
IF (NEW.ID_EPSD IS NOT NULL)
THEN
SET V_TABLE_ERROR ='EPISODE';
SET V_FL_OVERRIDE_PRNT=(SELECT
FL_OVERRIDE_PRNT
FROM EPISODE WHERE ID_EPSD = NEW.ID_EPSD);
SET V_FL_OVERRIDE_PRNT_SRVC = (SELECT FL_OVERRIDE_PRNT
FROM SERVICE_TYPE WHERE CD_SRVC = NEW.CD_SRVC);
IF (NEW.ID_PRVD_ORG <> NEW.ID_BSNS) AND
(NEW.ID_EPSD IS NOT NULL) AND
(V_FL_OVERRIDE_PRNT = 'N') AND
(V_FL_OVERRIDE_PRNT_SRVC = 'N') AND
( ( NEW.CD_PMNT_TYPE <> 'C' AND
(
( V_FL_FFP = 'Y' AND
( NEW.AM_ADMIN <> NEW.AM_RQST ) )
OR
( NEW.AM_ADMIN = NEW.AM_RQST )
)
)
OR
( NEW.CD_PMNT_TYPE = 'C' AND
V_FL_FFP = 'Y' ))
THEN
SET NEW.ID_PAYEE = NEW.ID_BSNS;
ELSE
SET NEW.ID_PAYEE = NEW.ID_PRVD_ORG;
END IF;
ELSE
SET V_TABLE_ERROR ='EPISODE';
SET NEW.ID_PAYEE = NEW.ID_PRVD_ORG;
END IF;
END
Start Free Trial
