CREATE OR REPLACE TRIGGER Ustaw_null_dept
BEFORE DELETE OR UPDATE OF deptno
ON dept
FOR EACH ROW
BEGIN
  --IF DELETING THEN
  --  DBMS_OUTPUT.Put_Line('OLD-'||:OLD.deptno||', NEW-'||:NEW.deptno);
  --END IF;
  IF UPDATING AND :OLD.deptno <> :NEW.deptno OR DELETING THEN
    UPDATE emp
    SET deptno = null
    WHERE deptno = :OLD.deptno;
  END IF;
END;
/

SELECT ename FROM emp WHERE deptno = 10;
DELETE FROM dept WHERE deptno = 10;
SELECT ename FROM emp WHERE deptno IS NULL;
ROLLBACK;

DROP TRIGGER Ustaw_null_dept;
