Re: Problem with mutating table in Oracle triggers

hemlata wrote:

> 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

Your problem is a common one and one of the reasons why most developers only use triggers when they are absolutely essential.

Some trigger advocate may help you with how to solve this problem with triggers if such a solution exists. I strongly urge you to dispense with triggers and perform your table maintenance through procedures in a package. You will get better performance, more stability, easier debugging, more capabilities, and no mutating anything errors.

Daniel A. Morgan Received on Mon May 28 2001 - 09:16:35 CDT