Oracle trigger problem

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,
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;

Open in new window

gahuteAsked:
Who is Participating?
 
cyberkiwiCommented:
Error:  ORA-04091: table name is mutating, trigger/function may not see it

You cannot query the table that is causing the trigger!
0
 
cyberkiwiCommented:
You can force the rule to be broken using
http://searchoracle.techtarget.com/answer/Resolving-errors-ORA-04091-ORA-06512-and-ORA-04088

That is,

CREATE OR REPLACE TRIGGER TRG_LIVRAISON_CHKQTE
BEFORE INSERT OR UPDATE OF QTELIVR ON LIVRAISON
FOR EACH ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
DECLARE
0
 
gahuteAuthor Commented:
So I can't make a SELECT on LIVRAISON because LIVRAISON is causing the trigger?

How can I do what I want to do? Do you have an example on how to do what I want?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
gahuteAuthor Commented:
I don't like to read "is potentially dangerous" in the link you sent to me...
0
 
cyberkiwiCommented:
Yes, that's not dangerous for Oracle itself, but more for you because something has happened that is inserting into/updating the table, and in the trigger, you want to query the same table that is in limbo - data is trying to get inserted/updated.
You need to use it to break the rule - that's what the PRAGMA is for
0
 
gahuteAuthor Commented:
So I just need to add: "PRAGMA AUTONOMOUS_TRANSACTION;" in DECLARE section?
0
 
cyberkiwiCommented:
Yes that's right.
0
 
gahuteAuthor Commented:
Now I get the following error message (excuse me but it's in french...):

Erreur lors de l'enregistrement de la table "Db_01"."LIVRAISON" :
Ligne 25 : ORA-04098: Déclencheur  'Db_01.TRG_LIVRAISON_CHKQTE' non valide. Echec de la revalidation
0
 
gahuteAuthor Commented:
I give you the points since you gave me the solution for the problem I had.
0
 
cyberkiwiCommented:
Can you run this?

show errors trigger TRG_LIVRAISON_CHKQTE;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.