ORA-04091: table is mutating when using cursor in trigger to insert in other table
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
oracle triggers
asked Nov 20 '18 at 3:47
Pruthvi GandhiPruthvi Gandhi
33
add a comment |
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
oracle triggers
asked Nov 20 '18 at 3:47
Pruthvi GandhiPruthvi Gandhi
33
Possible duplicate of ORA-04091: table xx_xx is mutating, trigger/function may not see it
– default locale
Nov 20 '18 at 4: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: stackoverflow.com/questions/8167200/… stackoverflow.com/questions/34227363/…
– default locale
Nov 20 '18 at 4: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 4:49
Sorry, I didn't understand your code sample at first. The problem is that you're trying to select fromempand this can't be done in the trigger onemp. You might need to rethink your approach. Perhaps, you can use:new.dnoto update the count for this particular record, instead of inserting the whole table every time.
– default locale
Nov 20 '18 at 5:02
Okay, thanks for the help
– Pruthvi Gandhi
Nov 20 '18 at 6:02
add a comment |
0
0
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
oracle triggers
asked Nov 20 '18 at 3:47
Pruthvi GandhiPruthvi Gandhi
33
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
oracle triggers
oracle triggers
asked Nov 20 '18 at 3:47
Pruthvi GandhiPruthvi Gandhi
33
asked Nov 20 '18 at 3:47
Pruthvi GandhiPruthvi Gandhi
33
edited Nov 20 '18 at 4:30
Pruthvi Gandhi
asked Nov 20 '18 at 3:47
Pruthvi GandhiPruthvi Gandhi
33
asked Nov 20 '18 at 3:47
Pruthvi GandhiPruthvi Gandhi
33
asked Nov 20 '18 at 3:47
Pruthvi GandhiPruthvi Gandhi
33
33
Possible duplicate of ORA-04091: table xx_xx is mutating, trigger/function may not see it
– default locale
Nov 20 '18 at 4: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: stackoverflow.com/questions/8167200/… stackoverflow.com/questions/34227363/…
– default locale
Nov 20 '18 at 4: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 4:49
Sorry, I didn't understand your code sample at first. The problem is that you're trying to select fromempand this can't be done in the trigger onemp. You might need to rethink your approach. Perhaps, you can use:new.dnoto update the count for this particular record, instead of inserting the whole table every time.
– default locale
Nov 20 '18 at 5:02
Okay, thanks for the help
– Pruthvi Gandhi
Nov 20 '18 at 6:02
add a comment |
Possible duplicate of ORA-04091: table xx_xx is mutating, trigger/function may not see it
– default locale
Nov 20 '18 at 4: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: stackoverflow.com/questions/8167200/… stackoverflow.com/questions/34227363/…
– default locale
Nov 20 '18 at 4: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 4:49
Sorry, I didn't understand your code sample at first. The problem is that you're trying to select fromempand this can't be done in the trigger onemp. You might need to rethink your approach. Perhaps, you can use:new.dnoto update the count for this particular record, instead of inserting the whole table every time.
– default locale
Nov 20 '18 at 5:02
Okay, thanks for the help
– Pruthvi Gandhi
Nov 20 '18 at 6:02
Possible duplicate of ORA-04091: table xx_xx is mutating, trigger/function may not see it
– default locale
Nov 20 '18 at 4:16
Possible duplicate of ORA-04091: table xx_xx is mutating, trigger/function may not see it
– default locale
Nov 20 '18 at 4: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: stackoverflow.com/questions/8167200/… stackoverflow.com/questions/34227363/…
– default locale
Nov 20 '18 at 4:22
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: stackoverflow.com/questions/8167200/… stackoverflow.com/questions/34227363/…
– default locale
Nov 20 '18 at 4: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 4:49
I have edited the post, explaining what happens with the last insert, sorry for less information at first.
– Pruthvi Gandhi
Nov 20 '18 at 4: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 5:02
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 5:02
Okay, thanks for the help
– Pruthvi Gandhi
Nov 20 '18 at 6:02
Okay, thanks for the help
– Pruthvi Gandhi
Nov 20 '18 at 6:02
add a comment |
1 Answer
1
active
oldest
votes
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
answered Nov 20 '18 at 5:28
Kaushik NayakKaushik Nayak
19.3k41331
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 6: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 6:06
Okay, sorry my mistake. Thanks for your help..👍🏻
– Pruthvi Gandhi
Nov 20 '18 at 7:00
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53385936%2fora-04091-table-is-mutating-when-using-cursor-in-trigger-to-insert-in-other-tab%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 20 '18 at 5:28
Kaushik NayakKaushik Nayak
19.3k41331
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 6: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 6:06
Okay, sorry my mistake. Thanks for your help..👍🏻
– Pruthvi Gandhi
Nov 20 '18 at 7:00
add a comment |
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
answered Nov 20 '18 at 5:28
Kaushik NayakKaushik Nayak
19.3k41331
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 6: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 6:06
Okay, sorry my mistake. Thanks for your help..👍🏻
– Pruthvi Gandhi
Nov 20 '18 at 7:00
add a comment |
0
0
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
answered Nov 20 '18 at 5:28
Kaushik NayakKaushik Nayak
19.3k41331
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
answered Nov 20 '18 at 5:28
Kaushik NayakKaushik Nayak
19.3k41331
answered Nov 20 '18 at 5:28
Kaushik NayakKaushik Nayak
19.3k41331
answered Nov 20 '18 at 5:28
Kaushik NayakKaushik Nayak
19.3k41331
answered Nov 20 '18 at 5:28
Kaushik NayakKaushik Nayak
19.3k41331
19.3k41331
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 6: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 6:06
Okay, sorry my mistake. Thanks for your help..👍🏻
– Pruthvi Gandhi
Nov 20 '18 at 7:00
add a comment |
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 6: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 6:06
Okay, sorry my mistake. Thanks for your help..👍🏻
– Pruthvi Gandhi
Nov 20 '18 at 7:00
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 6:01
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 6: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 6:06
@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 6:06
Okay, sorry my mistake. Thanks for your help..👍🏻
– Pruthvi Gandhi
Nov 20 '18 at 7:00
Okay, sorry my mistake. Thanks for your help..👍🏻
– Pruthvi Gandhi
Nov 20 '18 at 7:00
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53385936%2fora-04091-table-is-mutating-when-using-cursor-in-trigger-to-insert-in-other-tab%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Possible duplicate of ORA-04091: table xx_xx is mutating, trigger/function may not see it
– default locale
Nov 20 '18 at 4: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: stackoverflow.com/questions/8167200/… stackoverflow.com/questions/34227363/…
– default locale
Nov 20 '18 at 4: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 4:49
Sorry, I didn't understand your code sample at first. The problem is that you're trying to select from
empand this can't be done in the trigger onemp. You might need to rethink your approach. Perhaps, you can use:new.dnoto update the count for this particular record, instead of inserting the whole table every time.– default locale
Nov 20 '18 at 5:02
Okay, thanks for the help
– Pruthvi Gandhi
Nov 20 '18 at 6:02