How to update a column in same table using calculated value in oracle trigger

Question

I am creating a trigger in oracle where I need to update a column "subtotal" whenever there is an update in column "quantity" of the same table. so my update command which should fire this trigger is :

Update TABLENAME set QUANTITY = 6 where Order_ID = 601 AND ITEM_SEQ = 4 ;

This should fire the following trigger i have :

AFTER UPDATE ON TABLENAME 
FOR EACH ROW 
BEGIN 
   IF UPDATING THEN UPDATE TABLENAME
          SET SUBTOTAL =  :NEW.QUANTITY * ACTUAL_PRICE
          WHERE  order_id = :NEW.order_id;
   END IF;
END; 

However i am getting a mutating error issue ORA-04091. I searched and found that this is generally when the table is updating and we try to insert values to it , however i used "AFTER" command and so the table should have been updated by then. Any help ?

Answer 1

My assumption is that you are merely trying to update the particular row that caused the trigger to fire. And that actual_price is also coming from the row being modified. If so, you'd do that simply by assigning a value to the :new.subtotal. But you'd have to do it in a before update trigger not an after update trigger.

create or replace trigger your_trigger_name
  before update on your_table_name
  for each row
begin
  :new.subtotal := :new.quantity * :new.actual_price;
end;

If this is not a homework assignment, it would be more efficient and less error-prone to define subtotal as a computed column in the table rather than trying to maintain the data in a trigger.