Problem solve Get help with specific problems with your technologies, process and projects.

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
if inserting then
insert into y
select * from x where ac#=:new.ac#;
end if;
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
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 
        type type_array is table of x.type_code%type index by binary_integer; 

        v_type  type_array; 
        type_empty      type_array; 


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

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


/* 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 
    x_pkg.v_type(x_pkg.v_type.COUNT+1) := :new.type_code; 

/* 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 


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; 


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.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.