Home » Other » Training & Certification » How to overcome Oracle trigger mutation error?
How to overcome Oracle trigger mutation error? [message #325920] Mon, 09 June 2008 09:10 Go to next message
fresher
Messages: 5
Registered: June 2008
Junior Member
I have a product table as shown in the following.

create table product
( prod_code NUMBER
, prod_name VARCHAR2(100)
, color VARCHAR2(20)
, quantity NUMBER);


After insert each product into the product table, if the product color is "Black", I want the system to automatically add another row of record with the same product code, product name and quantity but with color = "White".
So, I created a trigger in the following.

CREATE OR REPLACE TRIGGER trig_white_product
AFTER INSERT ON product
FOR EACH ROW
BEGIN
IF :NEW.color = "Black" THEN
INSERT INTO product (prod_code, prod_name, color, quantity) VALUES (:NEW.prod_code, :NEW.prod_name, "White", :NEW.quantity);
END IF;
END;
/



INSERT INTO product values (100, 'Product 01', 'Black', 20);

But when the trigger references the product table that owns the trigger, an error occurs "ORA-04091: table name is mutating, trigger/function may not see it.".
Does anyone know how to solve this mutating error?
Thanks in advance.
Re: How to overcome Oracle trigger mutation error? [message #325922 is a reply to message #325920] Mon, 09 June 2008 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
>Does anyone know how to solve this mutating error?
I am sorry to see that both the search function on this forum & GOOGLE are broken for you.
Please be patient while repairs are completed.
A more detailed response will follow.
Re: How to overcome Oracle trigger mutation error? [message #325929 is a reply to message #325920] Mon, 09 June 2008 10:22 Go to previous message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it that way as it is not allowed to read or write the table you are modifying inside a row level trigger.
You have to record somewhere the rows you insert and insert the added ones inside an after statement level trigger.

Regards
Michel
Previous Topic: Fastest way to learn PL/SQL
Next Topic: What happens when a database crashes?
Goto Forum:
  


Current Time: Tue Sep 02 03:42:50 CDT 2014

Total time taken to generate the page: 0.09346 seconds