Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Trigger..... Help Me
Mutating Trigger..... Help Me [message #276309] Wed, 24 October 2007 14:50 Go to next message
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 #276310 is a reply to message #276309] Wed, 24 October 2007 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Is GOOGLE and the SEARCH button on this forum broken for you?
Re: Mutating Trigger..... Help Me [message #276311 is a reply to message #276310] Wed, 24 October 2007 14:59 Go to previous messageGo to next message
quakeworld2007@gmail.com
Messages: 9
Registered: October 2007
Junior Member
I have already tried google , unless u got some links that you know of
Re: Mutating Trigger..... Help Me [message #276312 is a reply to message #276309] Wed, 24 October 2007 15:06 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
select order_id into the_order_id
from product_order
where order_id = :new.order_id;
Amazing.
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 Go to previous message
Littlefoot
Messages: 20901
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;
However, if you decide to accept these suggestions, you'll have to modify trigger declaration - it can not be AFTER insert, but BEFORE (otherwise, you'll end up with an error).

Here is a link to the Using triggers chapter of the Oracle Documentation. Perhaps you'll want to read it.
Previous Topic: how to check if job is running from different session
Next Topic: Duplicate value in a single column
Goto Forum:
  


Current Time: Fri Dec 09 07:34:51 CST 2016

Total time taken to generate the page: 0.05491 seconds