When a certain record type is deleted on table 1, I am trying to delete rows from 2 different tables which are tied by RI. Because our installation uses "delete restrict" I can't use an "after delete" trigger.
Any help will be appreciated.
Thanks,
Barb
CREATE TRIGGER DEL
NO CASCADE BEFORE
DELETE
ON TABLE1
REFERENCING OLD AS OLDTAB
FOR EACH ROW MODE DB2SQL
WHEN (OLDTAB.SECT_CD = 'B ')
BEGIN ATOMIC
DELETE
FROM TABLE2 T2
WHERE T2.FK_CASEIDN =
(SELECT T3.IDNBR
FROM TABLE3 T3
WHERE T3.FK_INVSCASEID = OLDTAB.FK_CASEID
AND T3.FK_INVS_CAID = OLDTAB.IDNBR)
;
DELETE
FROM TABLE3 T3
WHERE T3.FK_INVSCASEID = OLDTAB.FK_CASEID
AND T3.FK_INVS_CAID = OLDTAB.IDNBR
;
END
Any help will be appreciated.
Thanks,
Barb
CREATE TRIGGER DEL
NO CASCADE BEFORE
DELETE
ON TABLE1
REFERENCING OLD AS OLDTAB
FOR EACH ROW MODE DB2SQL
WHEN (OLDTAB.SECT_CD = 'B ')
BEGIN ATOMIC
DELETE
FROM TABLE2 T2
WHERE T2.FK_CASEIDN =
(SELECT T3.IDNBR
FROM TABLE3 T3
WHERE T3.FK_INVSCASEID = OLDTAB.FK_CASEID
AND T3.FK_INVS_CAID = OLDTAB.IDNBR)
;
DELETE
FROM TABLE3 T3
WHERE T3.FK_INVSCASEID = OLDTAB.FK_CASEID
AND T3.FK_INVS_CAID = OLDTAB.IDNBR
;
END