Reputation: 11
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
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