0
create table dept(dno number(3) primary key)
create table emp(eno number(3) primary key,dno number(3) references dept)
create table emp_cnt(dno number(3),cnt number(3),foreign key(dno) references dept)

insert all
into dept values(101)
into dept values(102)
into dept values(103)
into dept values(104)
into dept values(105)
select * from dual

create or replace trigger count_emp after insert or update or delete on emp for each row
declare
cursor c1 is select dno,count(eno) cnt from emp group by dno;
begin
for row in c1
loop
insert into emp_cnt(dno,cnt) values(row.dno,row.cnt);
end loop;
end;

insert into emp values(1,101)

when I try to insert the data in the 'emp' table like the above statement, it shows me an error telling that my 'emp' table is mutating. Below i have shown the exact error that it shows

ORA-04091: table SYSTEM.EMP is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.COUNT_EMP", line 2
ORA-06512: at "SYSTEM.COUNT_EMP", line 4
ORA-04088: error during execution of trigger 'SYSTEM.COUNT_EMP'
1. insert into emp values(1,101)

with the last insert I'm inserting in the 'emp' table, which will invoke the trigger 'emp_count', in this trigger I'm using cursor to count the number of employees in each department and then I'm insert that data of cursor in the 'emp_cnt' table

Pruthvi Gandhi
  • 3
  • 3
  • Possible duplicate of [ORA-04091: table xx\_xx is mutating, trigger/function may not see it](https://stackoverflow.com/questions/36723587/ora-04091-table-xx-xx-is-mutating-trigger-function-may-not-see-it) – default locale Nov 20 '18 at 04:16
  • You can't insert in the table after insert on the same table, even if it worked it would result in an infinite loop. You might want to explain what exactly are you trying to achieve with this last insert. Anyway, see this questions: https://stackoverflow.com/questions/8167200/insert-trigger-for-inserting-record-in-same-table https://stackoverflow.com/questions/34227363/triggers-insert-same-table-plsql – default locale Nov 20 '18 at 04:22
  • I have edited the post, explaining what happens with the last insert, sorry for less information at first. – Pruthvi Gandhi Nov 20 '18 at 04:49
  • Sorry, I didn't understand your code sample at first. The problem is that you're trying to select from `emp` and this can't be done in the trigger on `emp`. You might need to rethink your approach. Perhaps, you can use `:new.dno` to update the count for this particular record, instead of inserting the whole table every time. – default locale Nov 20 '18 at 05:02
  • Okay, thanks for the help – Pruthvi Gandhi Nov 20 '18 at 06:02

1 Answers1

0

As mentioned in the comments, you cannot query or modify the table which is the Trigger owner, which if you do causes the error.

A Trigger is not meant for such requirements. Use a View instead.

create or replace view  emp_cnt
 AS
 select dno,count(eno) cnt from emp 
group by dno;


insert into emp values(1,101);
insert into emp values(2,101);
insert into emp values(3,102);


select * from  emp_cnt;

DNO CNT
102 1
101 2

Demo

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • I’m not modifying the ‘emp’ table, which is the owner of the trigger, i’m just firing a SELECT query on that table. I’m modifying the ‘emp_cnt’ table – Pruthvi Gandhi Nov 20 '18 at 06:01
  • @PruthviGandhi : Please read it again, I said, "*cannot query or modify the table*" which means you can't even do a select. Please refer to the links referred to in the comment under the question to understand more. – Kaushik Nayak Nov 20 '18 at 06:06