i made trigger to update data when people make action in form-CodePudding

trigger code good but when make action this error appear

error ORA-04091: table OR_HR.SELL is mutating, trigger/function may not see it.

trigger code

create or replace TRIGGER  "QUALITY_EDIT" 
AFTER INSERT OR UPDATE ON Sell
FOR EACH ROW
BEGIN 
UPDATE  DRUG
SET QUANTITY =
(SELECT (DRUG.QUANTITY - SELL.QUANTITY )  FROM Sell
JOIN Drug
ON SELL.DRUG_ID = DRUG.DRUG_ID) ;
END;

How can i solve this problem?

CodePudding user response：

You can't select from table which is just being updated (or inserted into), it is mutating and trigger can't see it.

Lucky you, you don't have to select from sell, use something like this (the :new pseudorecord) instead:

create or replace trigger quality_edit
  after insert or update on sell
  for each row
begin
  update drug d set
    d.quantity = d.quantity - :new.quantity
    where d.drug_id = :new.drug_id;
end;
/