Hi All,
I am working on Oracle triggers.I am facing problem of mutating table in the trigger.. Please go through the schema described below ..
I have three tables in schema EMP ,DEPT and EMP_DEPT.
EMP : EMPNO(PK) ,NAME
DEPT :DEPTNO(PK) ,DEPTNAME
EMP_DEPT :EMPNO(FKEY TO EMP TABLE),
DEPTNO(FKEY TO DEPT TABLE)
Every employee belongs to at least one department i.e. every emplyoee belongs to one or more departments and the relation between EMP and DEPT table is described using EMP_DEPT table.
Now, EMP is parent table and EMP_DEPT is child table.
DEPT table by default contains some default departments and we can add new departments also.
EMP table has insert row trigger as :
-- By default , a employee is assigned to department no 1.
create trigger ti_emp AFTER Insert ON EMP FOR EACH ROW NEW AS NEW
begin
insert into EMP_DEPT (EMPNO,DEPTNO) values (:new.EMPNO, 1 );
end;
/
EMP_DEPT table has the following delete cascade constraint :
ALTER TABLE EMP_DEPT
ADD FOREIGN KEY (EMPNO)
REFERENCES EMP(EMPNO) ON DELETE CASCADE;
and following insert/ delete row/statement triggers :
create global temporary table EMP_DEPT_temp
(
EMPNO NUMBER;
DEPTNO NUMBER;
)
/
create trigger ti_EMP_DEPT AFTER Insert ON EMP_DEPT FOR EACH ROW
begin
INSERT INTO EMP_DEPT VALUES (:NEW.EMPNO,:NEW.DEPTNO);
end;
/
create or replace TRIGGER ti_EMP_DEPT2 after insert on Assignment
DECLARE
TEMP1 NUMBER;
begin
DELETE FROM EMP_DEPT WHERE (EMPNO) IN
(SELECT E.EMPNO FROM EMP_DEPT_TEMP E WHERE E.DEPTNO <> 1)
AND DEPTNO = 1;
DELETE EMP_DEPT_TEMP ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DELETE EMP_DEPT_TEMP;
end;
/
create global temporary table EMP_DEPT_TEMP1
(
EMPNO NUMBER;
);
/
CREATE or REPLACE trigger TD_EMP_DEPT AFTER Delete ON EMP_DEPT
FOR EACH ROW OLD AS OLD
begin
delete from EMP_DEPT_temp1;
INSERT INTO EMP_DEPT_temp1 VALUES (:old.EMPNO);
END;
/
CREATE or REPLACE TRIGGER TD_EMP_DEPT2 after delete on Assignment
declare
temp1 number := 0 ;
temp2 number := 0 ;
BEGIN
SELECT E1.EMPNO into temp1 FROM EMP E ,EMP_DEPT_temp1 E1
WHERE E.EMPNO = E1.EMPNO; /* source of error */
SELECT 1 INTO TEMP2 FROM EMP_DEPT E1, EMP_DEPT_temp1 E2
WHERE E1.CONTACTID = E2.CONTACTID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
if temp1 <> 0 then
INSERT INTO EMP_DEPT (EMPNO.DEPTNO)values
(temp1,1);
else
null;
end if;
end;
/
I am facing the problem , when i delete a row from EMP table.
Due to delete cascade restriction , the delete triggers on EMP_DEPT table get called and getting "mutating table error" in td_EMP_DEPT2 trigger for EMP table.
I am getting error because of the following statement in td_EMP_DEPT2 trigger.
SELECT E1.EMPNO into temp1 FROM EMP E ,EMP_DEPT_temp1 E1
WHERE E.EMPNO = E1.EMPNO;
But this check is required as i donno the exact event when the trigger td_EMP_DEPT2 is fired.
This trigger can be fired in two ways 1. when a row from EMP is deleted
2. when a row from EMP_DEPT table is deleted.
Can anybody tell me , how can I avoid this error in the specified scenario..?
Thanks,
Hemlata
[Edited by hemlata on 05-28-2001 at 06:49 AM]
