What is mutating table?




Answers were Sorted based on User's Feedback



What is mutating table?..

Answer / pavan_1981

A mutating table is a table that is currently being
modified by an update, delete, or insert statement. For
example, if your trigger contains a select statement or an
update statement referencing the table it is triggering off
of you will receive the error.
Another way this error can occur is if the trigger has
statements to change the primary, foreign or unique key
columns of the table the trigger is triggering off of.

Is This Answer Correct ?    122 Yes 5 No

What is mutating table?..

Answer / gopal

Mutatation of table is a state of table when in
before /after update trigger table tries to update the same
table.As the call of update also calls same trigger then
the trigger will be called infinite times. then the table
will be mutating

Is This Answer Correct ?    43 Yes 8 No

What is mutating table?..

Answer / kiran

Mutating" means "changing". A mutating table is a table
that is currently being modified by an update, delete, or
insert statement. When a trigger tries to reference a table
that is in state of flux (being changed), it is
considered "mutating", and raises an error since Oracle
should never return inconsistent data.

Another way this error can occur is if the trigger has
statements to change the primary, foreign or unique key
columns of the table off which it fires. If you must have
triggers on tables that have referential constraints, the
workaround is to enforce the referential integrity through
triggers as well.

Is This Answer Correct ?    36 Yes 5 No




What is mutating table?..

Answer / swapnil siriah

A mutating table is a table that is currently being
modified by an UPDATE, DELETE, or INSERT statement, or it
is a table that might need to be updated by the effects of
a declarative DELETE CASCADE referential integrity
constraint. The restrictions on such a table apply only to
the session that issued the statement in progress.
For all row triggers, that were fired as the result of a
DELETE CASCADE, there are two important restrictions
regarding mutating tables. These restrictions prevent a
trigger from seeing an inconsistent set of data.
The SQL statements of a trigger cannot read from (query) or
modify a
mutating table of the triggering statement.

For eg:We have two tables "A" and "B". "A" is the master
table and "B" the detail table. We specified a foreign key
between "B" and "A" with the CASCADE DELETE option.

Here are the CREATE statements
drop table B;
drop table A;
create table A (
ida number not null,
vala varchar2(10),
primary key(ida));
create table B (
idb number,
valb varchar2(10),
foreign key (idb) references A (ida) on delete cascade)
/
create or replace trigger b_br
after delete on B
for each row
declare
n integer;
begin
select count(*) into n from A;
dbms_output.put_line('there are ' || n || ' rows in A');
dbms_output.put_line('after statment on B');
dbms_output.new_line;
end;
/
insert into A values(1,'Table A');
insert into A values(2,'Table A');
insert into B values(1,'Table B');
insert into B values(1,'Table B');
commit;
set serveroutput on;
delete from A where idA = 1;
ERROR at line 1:
ORA-04091: table SCOTT.A is mutating, trigger/function may
not see
ORA-06512: at "SCOTT.B_BR", line 4
ORA-04088: error during execution of trigger 'SCOTT.B_BR'
Notice that the SQL statement ( "select count(*) into n
from A" ) is run for the first row of the table, and then
the AFTER row trigger B_BR is fired. In turn, a statement
in the AFTER row trigger body attempts to query the
original table A. However, because the table A is mutating
due to the CASCADE DELETE foreign key, this query is not
allowed by Oracle. If attempted, a runtime error occurs,
the effects of the trigger body and triggering statement
are rolled back, and control is returned to the user or
application.
Solution: Use statement trigger instead of row trigger
If you delete the line "FOR EACH ROW" from the trigger
above, then the trigger becomes a statement trigger, the
table is not mutating when the trigger fires, and the
trigger does output the correct data.
SQL> delete from A where idA = 1;

there are 1 rows in A
after statment on B

1 row deleted.
SQL> select count(*) from B;

COUNT(*)
----------
0
(PS:It is not always possible to change the row trigger to
a statement trigger. This is just a way of avoiding a
mutating table error , there are many other ways by which
this can be acheived)
Cheers,
SAS

Is This Answer Correct ?    34 Yes 3 No

What is mutating table?..

Answer / purushottam

All the above suggested point is fine but addition on it is
when you are going to use the aggregate function for same
table then also be the mutating table error will occur.

example:

create table t(x numner);
/

create or replace trigger t_af_trigg
before insert into t
for each row
declare
n integer;
begin
select count(*) into n from t;
dbms_output.put_line('there are ' || n || ' rows in t');
end;
/

insert into t values(1);
error:
ORA-04091: table MISC.T is mutating, trigger/function may
not see it
ORA-06512: at "MISC.T_AF_TRIGG", line 5
ORA-04088: error during execution of trigger 'MISC.T_AF_TRIGG'

This example value addition of Vivek's suggession

"Mutating means that some one is trying to access the table
currently being held by some other non-committing
transaction(i.e. in Locked state)"

Is This Answer Correct ?    20 Yes 9 No

What is mutating table?..

Answer / pradeep

Mutatin table means when error occurs when we create a row
level trigger on a table that attempts to access the same
table inside the trigger body

A Row-level trigger can not read from or write to the table,
on which it is fired. However a statement level trigger can
perform these actions on the table on which it is written

Is This Answer Correct ?    8 Yes 0 No

What is mutating table?..

Answer / rick

A mutating table is a table that is currently being
modified by an UPDATE, DELETE, or INSERT statement, or it
is a table that might need to be updated by the effects of
a declarative DELETE CASCADE referential integrity
constraint. The restrictions on such a table apply only to
the session that issued the statement in progress.
For all row triggers, that were fired as the result of a
DELETE CASCADE, there are two important restrictions
regarding mutating tables. These restrictions prevent a
trigger from seeing an inconsistent set of data.
The SQL statements of a trigger cannot read from (query) or
modify a
mutating table of the triggering statement.

For eg:We have two tables "A" and "B". "A" is the master
table and "B" the detail table. We specified a foreign key
between "B" and "A" with the CASCADE DELETE option.

Here are the CREATE statements
drop table B;
drop table A;
create table A (
ida number not null,
vala varchar2(10),
primary key(ida));
create table B (
idb number,
valb varchar2(10),
foreign key (idb) references A (ida) on delete cascade)
/
create or replace trigger b_br
after delete on B
for each row
declare
n integer;
begin
select count(*) into n from A;
dbms_output.put_line('there are ' || n || ' rows in A');
dbms_output.put_line('after statment on B');
dbms_output.new_line;
end;
/
insert into A values(1,'Table A');
insert into A values(2,'Table A');
insert into B values(1,'Table B');
insert into B values(1,'Table B');
commit;
set serveroutput on;
delete from A where idA = 1;
ERROR at line 1:
ORA-04091: table SCOTT.A is mutating, trigger/function may
not see
ORA-06512: at "SCOTT.B_BR", line 4
ORA-04088: error during execution of trigger 'SCOTT.B_BR'
Notice that the SQL statement ( "select count(*) into n
from A" ) is run for the first row of the table, and then
the AFTER row trigger B_BR is fired. In turn, a statement
in the AFTER row trigger body attempts to query the
original table A. However, because the table A is mutating
due to the CASCADE DELETE foreign key, this query is not
allowed by Oracle. If attempted, a runtime error occurs,
the effects of the trigger body and triggering statement
are rolled back, and control is returned to the user or
application.
Solution: Use statement trigger instead of row trigger
If you delete the line "FOR EACH ROW" from the trigger
above, then the trigger becomes a statement trigger, the
table is not mutating when the trigger fires, and the
trigger does output the correct data.
SQL> delete from A where idA = 1;

there are 1 rows in A
after statment on B

1 row deleted.
SQL> select count(*) from B;

COUNT(*)
----------
0
(PS:It is not always possible to change the row trigger to
a statement trigger. This is just a way of avoiding a
mutating table error , there are many other ways by which
this can be acheived)
Cheers,
SAS

Is This Answer Correct ?    4 Yes 0 No

What is mutating table?..

Answer / kaushik

A mutating table is a table that is currently being modified
by an UPDATE, INSERT or DELETE statement, or a table that
might need to be updated by the effects of a declarative
DELETE CASCADE referential integrity action. A table is not
considered mutating for STATEMENT triggers.

The triggered table itself is a mutating table as well as
any table referencing it with the foreign key constraint.
This restriction prevents a row trigger from seeing an
inconsistent set of data.

Is This Answer Correct ?    4 Yes 1 No

What is mutating table?..

Answer / koteswara reddy

If row level trigger based on a table than trigger body cannot read data from same table and also we can't perform dml operations on a same table. if anybody trying this then oracle server returns an error. this error is called mutating error and table is called mutating table
ex:-
create or replace trigger trg
after delete on emp
for each row
declare
l_count number;
begin
select count(*) into l_count from emp;
dbms_output.put_line(l_count);
end;
/

Is This Answer Correct ?    2 Yes 0 No

What is mutating table?..

Answer / rami reddy

CREATE TABLE STUDENT(SNO NUMBER(4),SNAME VARCHAR2(20),COURSE VARCHAR2(20));
ANOTHER TABLE
CREATE TABLE NEW_STUDENT(SNO NUMBER(4),SNAME VARCHAR2(20));
NOW WECAN CREATE TRIGGER FOR AUTO_INSERT FROM TABLE STUDENT TO NEW_STUDENT.AT THAT WE CAN USE 'AFTER INERT' AND 'SELECT' IN THAT TRIGGER, AT THAT WE GET THIS TYPE OF ERROR

1 CREATE OR REPLACE TRIGGER ATUO_INSERT1
2 AFTER INSERT ON STUDENT
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO NEW_STUDENT
6 SELECT :NEW.SNO,:NEW.SNAME FROM STUDENT;
7* END AUTO_INSERT1;
SQL> /

Trigger created.

SQL> INSERT INTO STUDENT VALUES(120,'RAJ','ORACLE');
INSERT INTO STUDENT VALUES(120,'RAJ','ORACLE')
*
ERROR at line 1:
ORA-04091: table SCOTT.STUDENT is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.ATUO_INSERT1", line 2
ORA-04088: error during execution of trigger 'SCOTT.ATUO_INSERT1'

Is This Answer Correct ?    1 Yes 1 No

Post New Answer



More SQL PLSQL Interview Questions

Difference between SUBSTR and INSTR?

3 Answers  


I want to create synonym for table emp but in my pc it is giving insufficient previliges.I am using user scott.Please suggest me.

3 Answers  


How to display all Friday's in a year with date?

3 Answers   Flipkart,


I have 2 table A and B. In A 1 lakh record is present. In b 20 thousand data is present. To get the unique data from table A and B which join we should prefer left inner join or right outer join. Please answer.

4 Answers   GE,


Write a query to genarate target column.Please answer me. Advance Thanks. Src Tgt Q10 Quarter to 2010 Q90 Quarter to 1990 Q80 Quarter to 1980 Q74 Quarter to 1974

3 Answers   Infosys,






Difference between IN and EXISTS

4 Answers   Polaris, Nous,


How would you hide a table in sql. ie the table can be only visible to its maker?

3 Answers  


how i do the database testing of online money transactions between two parties (client and organization). suppose our company makes a website for our client and give a extra feature of online money transaction with there clients then how i test this feature in database testing

1 Answers  


Wat is SGA ?

3 Answers   ABB,


HOW TO APPEAR FOR ONLINE EXAMS OF SQL & PL/SQL? WHICH BOOKS WILL BE BETTER FOR REFERENCE TO QUALIFY THESE EXAMS? ARE THERE ANY MODEL PAPERS AVAILABLE IN THE MARKET ABOUT SQL & PLSQL?

2 Answers  


hi,i plan to put experience on PLSQL ,can anyone suggest me for any institutes in bangalore or how to prepare for interviews

0 Answers  


What are wait events. Describe the wait event tables.

1 Answers   CTS,






Categories