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.
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