DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-04091: table SCOTT.EMP is mutating

  1. #1
    Join Date
    Feb 2001
    Posts
    119

    ORA-04091: table SCOTT.EMP is mutating

    wHEN I delete the any deptno in dept ,I want to backup the empno,ename and deptno in emp_log table for the corresping rows in emp table 's deptno.Cannot drop the cascade delete constarint.Any help pls.

    CREATE TABLE DEPT
    (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14) ,
    LOC VARCHAR2(13) ) ;


    CREATE TABLE EMP
    (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT on delete cascade);

    INSERT INTO DEPT VALUES
    (10,'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO DEPT VALUES
    (30,'SALES','CHICAGO');
    INSERT INTO DEPT VALUES
    (40,'OPERATIONS','BOSTON');
    INSERT INTO EMP VALUES
    (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
    INSERT INTO EMP VALUES
    (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

    create table emp_log as select empno,ename,deptno from emp where rownum <1;


    create or replace trigger dept_befdel_trg before delete
    on dept for each row
    begin
    insert into emp_log (empno,ename,deptno)
    select empno,ename,deptno from emp where deptno=ld.deptno;
    end ;

    SQL> delete from dept where deptno=20;
    delete from dept where deptno=20
    *
    ERROR at line 1:
    ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
    ORA-06512: at "SCOTT.DEPT_BEFDEL_TRG", line 2
    ORA-04088: error during execution of trigger 'SCOTT.DEPT_BEFDEL_TRG'
    Last edited by Subha; 10-05-2005 at 01:48 PM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The on-delete cascade foreign key causes the EMP table to be modified when the row from DEPT is deleted, therefore the trigger is not allowed to see it. How about placing the trigger on EMP instead? That might work as the trigger would be able to see the old values of the row and insert them directly into the log table without having to query the EMP table.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2001
    Posts
    119
    CREATE OR REPLACE PROCEDURE deleteby(
    l_deptno in number)
    IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    insert into emp_log (empno,ename,deptno)
    select empno,ename,deptno from emp where deptno=l_deptno;
    insert into temp(deptno) values(l_deptno);
    commit;
    end;

    CREATE OR REPLACE TRIGGER TU_A
    before DELETE
    ON DEPT
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
    deleteby(ld.deptno);
    END;

    This did the Intended Job.Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width