ORA-01779: cannot modify a column which maps to a non key-preserved table

USING INSTEAD OF TRIGGER TO AVOID ORA-01779 WHILE UPDATING VIEW


1. Create a view using EMP and DEPT

SQL> CREATE VIEW EMP_DEPT_VW AS
  2  SELECT EMPNO, ENAME, SAL, D.DEPTNO, DNAME
  3  FROM SCOTT.EMP E, SCOTT.DEPT D
  4  WHERE E.DEPTNO=D.DEPTNO
  5  ORDER BY EMPNO, D.DEPTNO;

View created.



2. if we try to Update DNAME, it will throw an execption

SQL> update EMP_DEPT_VW set DNAME='Rnd' where DEPTNO=20;
update EMP_DEPT_VW set DNAME='Rnd' where DEPTNO=20
                       *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


3. Need to create a INSTEAD of Trigger on View

CREATE OR REPLACE TRIGGER EMP_DEPT_VW
INSTEAD OF UPDATE ON EMP_DEPT_VW
FOR EACH ROW
DECLARE
    V_DEPTNO SCOTT.DEPT.DEPTNO%TYPE;
BEGIN
    SELECT DEPTNO INTO V_DEPTNO FROM SCOTT.EMP
    WHERE EMPNO=:NEW.EMPNO;
  
    UPDATE SCOTT.DEPT
    SET DNAME = :NEW.DNAME
    WHERE DEPTNO=V_DEPTNO;
  
    UPDATE SCOTT.EMP
    SET ENAME = :NEW.ENAME,
    SAL=:NEW.SAL
    WHERE EMPNO=:NEW.EMPNO;
END;
/
  
  
4. Now Update will work fine

SQL> update EMP_DEPT_VW set DNAME='Rnd' where DEPTNO=20;

5 rows updated.