-- #############################################################################################
--
-- %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;
/
