Answer Posted / 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 |
Post New Answer View All Answers
What is the difference between clustered and non-clustered indexes?
What is the purpose of using pl/sql?
What is form and report?
What is the usage of when clause in trigger?
what is dbms? : Sql dba
What is use of package in pl sql?
How to return multiple rows from the stored procedure?
What is the difference between functions, procedures, and packages in pl/sql?
What is package in pl sql?
What is a crud api?
How long does it take to learn pl sql?
What is the difference between local and global temporary table?
How can we connect an Android App to an Oracle database and use the PL/SQL procedural code?
What is sql scripting?
What are functions in sql?
