Problem with mutating table in Oracle triggers

From: hemlata <hemlata_at_premiertechnologygroup.com>
Date: 27 May 2001 22:55:48 -0700
Message-ID: <ed2ca968.0105272155.77c79209@posting.google.com>

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                DEPT                EMP_DEPT

EMPNOPK)           DEPTNO(PK)          EMPNO(FKEY TO EMP TABLE)
NAME               DEPTNAME            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..?
Please reply as soon as possible.

Thanks,
Hemlata Received on Mon May 28 2001 - 00:55:48 CDT