I saw the following routine in the Oracle 8 Server Online
Documentation to implement an update cascade in Oracle. I have
include the routine below. I am confused about the section of
code in the "IF Updating" section, because I do not understand
why it is necessary to use the update_id field. I cannot
understand how the child record could have already been
updated. Any help would be appreciated.Here is the routine:
UPDATE Cascade Trigger for Parent Table
The following trigger ensures that if a department number is updated in the
DEPT table, this change is propagated to dependent foreign keys in the EMP
table:
- Generate a sequence number to be used as a flag for
- determining if an update has occurred on a column.
CREATE SEQUENCE update_sequence
INCREMENT BY 1 MAXVALUE 5000
CYCLE;
CREATE PACKAGE integritypackage AS
updateseq NUMBER;
END integritypackage;
CREATE or replace PACKAGE BODY integritypackage AS
END integritypackage;
ALTER TABLE emp ADD update_id NUMBER; -- create flag col.
CREATE TRIGGER dept_cascade1 BEFORE UPDATE OF deptno ON dept
DECLARE
dummy NUMBER;
- Before updating the DEPT table (this is a statement
- trigger), generate a new sequence number and assign
- it to the public variable UPDATESEQ of a user-defined
- package named INTEGRITYPACKAGE.
BEGIN
SELECT update_sequence.NEXTVAL
INTO dummy
FROM dual;
integritypackage.updateseq := dummy;
END;
CREATE TRIGGER dept_cascade2 AFTER DELETE OR UPDATE
OF deptno ON dept FOR EACH ROW
- For each department number in DEPT that is updated,
- cascade the update to dependent foreign keys in the
- EMP table. Only cascade the update if the child row
- has not already been updated by this trigger.
BEGIN
IF UPDATING THEN
UPDATE emp
SET deptno = :new.deptno,
update_id = integritypackage.updateseq /*from 1st*/
WHERE emp.deptno = :old.deptno
AND update_id IS NULL;
/* only NULL if not updated by the 3rd trigger
fired by this same triggering statement */
END IF;
IF DELETING THEN
- Before a row is deleted from DEPT, delete all
- rows from the EMP table whose DEPTNO is the same as
- the DEPTNO being deleted from the DEPT table.
DELETE FROM emp
WHERE emp.deptno = :old.deptno;
END IF;
END;
CREATE TRIGGER dept_cascade3 AFTER UPDATE OF deptno ON dept
BEGIN UPDATE emp
SET update_id = NULL
WHERE update_id = integritypackage.updateseq;
END;
Note:
Because this trigger updates the EMP table, the EMP_DEPT_CHECK trigger, if
enabled, is also fired. The resulting mutating table error is trapped by the
EMP_DEPT_CHECK trigger. You should carefully test any triggers that require
error trapping to succeed to ensure that they will always work properly in
your environment.
Received on Wed Dec 08 1999 - 12:27:39 CST
