-- ############################################################################################# -- -- %Purpose: Database Trigger to implement an UPDATE CASCADE with Oracle8i -- -- In Oracle8i the Referential Integrity is checked in the -- Trigger, therefore there is no mutating problem. However -- there is one problem with the following update: -- -- UPDATE dept set deptno = deptno + 10; -- -- This update will update all departments with deptno 10 -- to the already existing deptno 20, and triggers again -- this rows. Now all rows with deptno 20 will be changed -- to 30 and again all rows with deptno 30 will be changed -- to 40 and so on and on ... finally all rows have -- deptno = 50 ! -- -- Therefore it's not allowed to update to an existing -- primary key, if this primary key have any childs. -- -- ############################################################################################# -- CREATE OR REPLACE TRIGGER scott.dept_emp_update AFTER UPDATE ON SCOTT.DEPT REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE eDeptNoExists EXCEPTION; -- Check if child table have child records with this new -- deptno, this is not allowed. CURSOR curs_exists_deptno IS SELECT 'X' FROM emp WHERE deptno = :NEW.deptno; rtEmp curs_exists_deptno%ROWTYPE; BEGIN IF(:NEW.deptno <> :OLD.deptno) THEN OPEN curs_exists_deptno; FETCH curs_exists_deptno INTO rtEmp; IF (curs_exists_deptno%FOUND) THEN CLOSE curs_exists_deptno; RAISE eDeptNoExists; END IF; CLOSE curs_exists_deptno; UPDATE emp SET deptno = :NEW.deptno WHERE deptno = :OLD.deptno; END IF; EXCEPTION WHEN eDeptNoExists THEN RAISE_APPLICATION_ERROR (-20102,'Error: This Primary Key: ' ||TO_CHAR(:NEW.deptno)||' exists and has child rows in emp, this tiggers again an update and so on ...'); END dept_emp_update; /