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 last published in February 2002

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close