praveena mohan
praveena mohan

Reputation: 11

Unable to update complex view after defining instead of Trigger in Oracle. ORA-01779: cannot modify a column which maps to a non key-preserved table

I am receiving the error while updating the complex view even after the creation of Instead of Trigger.

ERROR:

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

UPDATE ON COMPLEX VIEW:

update employee_dep
  set dname='Admin',
      deptno=506
where empno=103;

INSTEAD OF TRIGGER(created successfully):

create or replace trigger trigg_namew 
instead of insert on employee_dep
for each row
declare 
  rcount number;
begin
  select count(*) into rcount from employ where empno = :new.empno;
  if (rcount = 0) then
    insert into employ (empno,deptno) values (:new.empno, :new.deptno);
  else
    update employ
      set deptno = :new.deptno
    where empno = :old.empno and deptno = :old.deptno;
  end if;
  select count(*) into rcount from department where deptno = :new.deptno;
  if(rcount = 0) then
    insert into department values (:new.deptno , :new.dname);
  else
    update department
      set dname = :new.dname
    where deptno = :old.deptno and dname = :old.dname;
 end if;
end;

Upvotes: 1

Views: 256

Answers (2)

Alvey
Alvey

Reputation: 21

You are issuing an update to a complex view with an instead of trigger which is only firing on insert.

Change to INSTEAD OF UPDATE

Upvotes: 2

Padders
Padders

Reputation: 276

Maybe it's this "instead of insert".

Upvotes: 1

Related Questions