Answer Posted / 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

how can we submit a form without a submit button? : Sql dba

916

---

Can a commit statement be executed as part of a trigger?

995

---

How do I truncate a sql log file?

1003

---

What are the types of dbms?

920

---

What does desc stand for?

1021

---

What are triggers and its uses?

1001

---

Which is faster union or join?

901

---

Is it important to partition hard disk?

965

---

what is index? : Sql dba

942

---

what is a table in a database ? : Sql dba

989

---

What are the parts of a sql statement?

1028

---

What is %type in pl sql?

924

---

Is it mandatory for the primary key to be given a value when a new record is inserted?

1139

---

Which sql statement is used to return only different values?

953

---

What is pl sql collection?

920

---