Question:

Hi experts,

I’m trying to create a trigger which will make sure I can’t enter a total value for my deliveries which would make the total quantity ordered is busted. Ex: I ordered 100 parts, delivered 25 the first time, 50 the second time, I can’t deliver more than 25 the third time.

The attached code doesn’t work. The compilation is ok but I get error ORA-04091. I  don’t understand why I get this message.

By the way, my first language is french.

Thanks,
Code Snippet:

CREATE OR REPLACE TRIGGER TRG_LIVRAISON_CHKQTE
BEFORE INSERT OR UPDATE OF QTELIVR ON LIVRAISON
FOR EACH ROW
DECLARE
QuantiteC NUMBER;
QteTotLivr NUMBER;
QteInvalide EXCEPTION;
BEGIN
SELECT QteComm INTO QuantiteC
FROM COMMANDE
WHERE NoComm = :NEW.NoComm;

SELECT SUM(QteLivr) INTO QteTotLivr
FROM LIVRAISON
WHERE NoComm = :NEW.NoComm;

QteTotLivr := QteTotLivr + :NEW.QteLivr;

IF QteTotLivr > QuantiteC THEN
RAISE QteInvalide;
END IF;

EXCEPTION
WHEN QteInvalide THEN
RAISE_APPLICATION_ERROR(-20001, ‘Erreur
20100420-2239′);
END;

Solution:

Error:  ORA-04091: table name is mutating, trigger/function may not see it

You cannot query the table that is causing the trigger!