-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|