| Mutating Trigger..... Help Me [message #276309] |
Wed, 24 October 2007 14:50  |
quakeworld2007@gmail.com
Messages: 9
Registered: October 2007
|
Junior Member |
|
|
I have this trigger that is suppose to add 6 to the total when a new row is inserted into the product_order table. My trigger compiles w/o any errors, but when I go to test it with this code ...
insert into product_order
values(1010, sysdate, (sysdate),'CC',130.95,102);
I get a mutating table error.. Anyone got an idea?
create or replace
TRIGGER ex3c_product_order
AFTER INSERT ON product_order
referencing new as new old as old
FOR EACH ROW
declare
the_order_id number;
begin
select order_id into the_order_id
from product_order
where order_id = :new.order_id;
if the_order_id is not null then
update product_order
set total = (total + 6)
where order_id = :new.order_id;
end if;
commit;
END;
|
|
|
|
|
|
|
|
| Re: Mutating Trigger..... Help Me [message #276312 is a reply to message #276309] |
Wed, 24 October 2007 15:06  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
select order_id into the_order_id
from product_order
where order_id = :new.order_id;
Why would you directly use :new.order_id (or simple assign it into the_order_id variable), when you may complicate it using SELECT.
As the side effect, you get the mutating error,as you query the table you currently update (the same row).
|
|
|
|
| Re: Mutating Trigger..... Help Me [message #276318 is a reply to message #276312] |
Wed, 24 October 2007 16:02 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
How does the 'product_order' table look like? Is there a chance that 'order_id' column might be NULL? Trigger you wrote suggests so (because you are checking its value). If so, why? From your example, it looks like a good candidate for a primary key constraint.
In my opinion, trigger might be very simple: BEGIN
:new.total := :new.total + 6;
END;
Here is a link to the Using triggers chapter of the Oracle Documentation. Perhaps you'll want to read it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
