CODEWITHSUNDEEP
sqloracle-database
korgidis
korgidis

Reputation: 5

How to syntax a trigger in SQL in order to UPDATE a column from the same table AFTER INSERT (oracle Database)

I have created this trigger so that after insert a row to ORDER_DETAIL table, to get the "UNIT_PRICE" from the table ITEM and to set it to the "PRICE" of the ORDER_DETAIL table based on the "ITEM_ID" .Do I have any errors to the syntax? Thank you in advance! 

CREATE OR REPLACE TRIGGER PRICETRIG
AFTER INSERT ON ORDER_DETAIL FOR EACH ROW
BEGIN 
UPDATE ORDER_DETAIL
SET ORDER_DETAIL.PRICE=(SELECT ITEM.UNIT_PRICE FROM ITEM WHERE 
ORDER_DETAIL.ITEM_ID=ITEM.ITEM_ID);
END;

thats the error: 

One error saving changes to table "ORDER_DETAIL":
Row 8: ORA-04091: table ORDER_DETAIL is mutating, trigger/function 
may not see it
ORA-06512: at "PRICETRIG", line 2
ORA-04088: error during execution of trigger 'PRICETRIG'
ORA-06512: at line 1

Upvotes: 0

Views: 69

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You should be referring to the NEW pseudorecord, not the table the trigger is on. It should also be a before-insert trigger, something like:

CREATE OR REPLACE TRIGGER PRICETRIG
BEFORE INSERT ON ORDER_DETAIL
FOR EACH ROW
BEGIN
  SELECT ITEM.UNIT_PRICE
  INTO :NEW.ORDER_PRICE
  FROM ITEM
  WHERE ITEM.ITEM_ID = :NEW.ITEM_ID;
END;

Your original code was trying to update every row in the table the trigger was against, not just the newly-inserted row.

You might want to check that you don't have, say, a quantity field on the order that you're supposed to multiply the unit price by.

Upvotes: 2

Related Questions