Q

Trigger causing table to mutate

I have been tring to create a trigger on a table, that when you insert something in (x) table, the trigger inserts this thing in (y) table. Successfully, I created a trigger, but when I inserted the value in the (x) table, the value did not insert and Oracle generated an error "the table x is mutating." Can you explain me this thing, as well as help me make the proper coding? Here is a copy of my coding:

create or replace trigger adeeb_finance
after insert on  x
for each row
begin
if inserting then
insert into y
select * from x where ac#=:new.ac#;
end if;
end;
Note: This trigger successfully executed, but when I inserted a row in x:
insert into x values(1,2,3,4,5);
following error occurs:
trigger adeeb_finance is invalid..failed
validation.
Often, I get this error too:
The table x is mutating


The law about triggers is that in the body of the trigger it is forbidden to do a DML operation on the table that fired the trigger. This is so because a trigger is considered a part of the DML operation that fired it. So, the SELECT statement from x that you're doing in the body of the trigger causes the table to mutate.

Reader Feedback

Darren M. writes: This is how you can get around the mutating table error when a trigger is fired:

/* Create a package with pl/sql table variables that 
are indexed by binary integer */ 

create or replace package x_pkg 
as 
        type type_array is table of x.type_code%type index by binary_integer; 

        v_type  type_array; 
        type_empty      type_array; 

end; 
/ 

/* Initialize the package variable before the insert on the table */ 

create or replace trigger x_bi1 
before INSERT on x 
begin 
        x_pkg.v_type    := x_pkg.type_empty; 

end; 
/ 

/* For each row affected by the insert on x, capture +
the value of the new type_code column and store it in 
x_pkg.v_type variable */

create or replace trigger x_bifer1 
before insert on x 
for each row 
begin 
    x_pkg.v_type(x_pkg.v_type.COUNT+1) := :new.type_code; 
        
end; 
/ 

/* After insert trigger on x to update y by selecting 
values from x based on the value of the package variable */ 

create or replace trigger x_ai1 
after INSERT on x 
declare 

begin 

for i in 1 .. x_pkg.v_type.count loop 

insert into y 
select * from x where ac#=x_pkg.v_type(i); 

end loop; 

end; 
/ 

I haven't tested this specific example, but I have used this coding style successfully in the past.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close