Ask the Expert

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


    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: