table mutating problem (trigger)
:confused: I have my trigger that run correctly without errors - but when I want to test it with : delete emp where deptno = 20;
it is giving me an error :
table xxx.EMP is mutating, trigger/function may not see it
the trigger does : when given a deptno it delete the dept of this emp (records of emp with deptno)
Help please.... Thanks!
here is the code:
show errors;
create or replace trigger deleteDEPT
after delete on EMP
for each row
declare
v_deptno EMP.deptno%Type;
counter EMP.deptno%Type;
begin
select count(*) into counter from EMP
where EMP.deptno = :OLD.deptno;
if counter = 0 then
delete DEPT;
--delete_dept(v_deptno);
end if;
end;
/
I tried to use this to resolve the problem, but nothing.
ALTER TABLE emp DROP CONSTRAINT fk_dept_deptno;
ALTER TABLE emp ADD (
CONSTRAINT fk_dept_deptno
FOREIGN KEY (DEPTNO)
REFERENCES DEPT(DEPTNO) ON DELETE CASCADE
);