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

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

Threaded View

  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.

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