| How to overcome Oracle trigger mutation error? [message #325920] |
Mon, 09 June 2008 09:10  |
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 #325929 is a reply to message #325920] |
Mon, 09 June 2008 10:22  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
