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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.