CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Clyon97
Clyon97

Reputation: 61

After Update mutating table

can someone tell me how to avoid this mutating table error (ORA-04091) I have to use a After Update Trigger for this homework.

AFTER UPDATE OF QTY,ACTUALPRICE  ON ITEM
FOR EACH ROW
DECLARE
BEGIN
    UPDATE ITEM
    SET ITEMTOT = :new.QTY * :new.ACTUALPRICE
    WHERE ORDID = :old.ORDID AND ITEMID = :old.ITEMID;
END;

Upvotes: 0

Views: 40

Answers (1)

GMB
GMB

Reputation: 222402

You cannot run a query againts the table on which the trigger fired within the trigger itself. In your case, I think that your trigger could be rewritten as a BEFORE UPDATE trigger that simply sets ITEMTOT:

BEFORE UPDATE OF QTY,ACTUALPRICE ON ITEM
FOR EACH ROW
    :new.ITEMTOT = :new.QTY * :new.ACTUALPRICE
END;

Upvotes: 1

Related Questions