Interested to Buy Any Domain ? << Click Here >> for more details...
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 are few of the schema objects that are created using PL/SQL?
What are inner outer left and right joins in sql?
What is varchar used for?
Explain the the update statement in sql?
What is meaning of <> in sql?
what are dynamic queries in t-sql? : Transact sql
What is java sql driver?
What is keys and its types?
What does an inner join do?
Why use stored procedures?
What is pragma in pl sql?
Differentiate pl/sql and sql?
What are crud methods?
What is query syntax?
What are local and global variables and their differences?
