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
| Is This Answer Correct ? | 4 Yes | 0 No |
Post New Answer View All Answers
What is the max nvarchar size?
How can get second highest salary in sql?
what are aggregate and scalar functions? : Sql dba
Why do we use procedures in sql?
Do we need to create index on primary key?
Does truncate release storage space?
What does dml mean?
Which table is left in left join?
how to drop an existing table in mysql? : Sql dba
Are left and right joins the same?
What is the use of index in hive?
How to process query result in pl/sql?
How do I debug a stored procedure?
Difference between table function and pipelined function?
What is primary key and unique key?
