I have been tring to create a trigger on a table, that when you insert something in (x) table, the trigger inserts...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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;
insert into x values(1,2,3,4,5);
trigger adeeb_finance is invalid..failed validation.
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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.