ORA-04091 on Create or Replace Trigger xxx Before Insert of Update on xxx

Question

Can someone help me correct the Trigger below? I am assigned this problem but my technical skill is limited.

My script hit the error below (ORA 4091):

Processing STANDARD PO Number: 27179

......................................

Updating PO Status..

Done Approval Processing.

dist id 611294gl amount 10000.88 bill_del_amount 0 l_amount 10000.88

some exception occured **ORA-04091: table PO.PO_DISTRIBUTIONS_ALL is mutating,

trigger/function may not see it**

ORA-06512: at "APPS.XXAET_PO_DELIVER_TO_TRG",

line 22

ORA-01403: no data found

ORA-04088: error during execution of trigger

'APPS.XXAET_PO_DELIVER

PL/SQL procedure successfully completed.

SQL>

I manage to find the customized trigger in clients machine, but after researching I am unable to pin point whats wrong with the sql . Please HELP!

CREATE OR REPLACE TRIGGER apps.xxaet_po_deliver_to_trg
BEFORE INSERT OR UPDATE ON po_distributions_all
   FOR EACH ROW
DECLARE
   l_emp_name   VARCHAR2 (300);
   l_sqlcode    VARCHAR (30)   := SQLCODE;
   l_sqlerrm    VARCHAR (400)  := SUBSTR (SQLERRM, 1, 399);
   x_profile_value  VARCHAR (10) ;
BEGIN

x_profile_value := fnd_profile.value('ORG_ID');
  Select Ship_To_Location_ID
  INTO :NEW.Deliver_To_Location_Id
  from PO_LINE_LOCATIONS_ALL
  WHERE line_location_id = :NEW.line_location_id
  AND ORG_ID = x_profile_value
  ;

EXCEPTION
   WHEN OTHERS
   THEN
      NULL;

   UPDATE PO_DISTRIBUTIONS_ALL SET Deliver_To_Location_Id = :NEW.Deliver_To_Location_Id
   WHERE line_location_id = :NEW.line_location_id;

END;

/

Thank you so much! Kenneth.

Answer 1

The root cause of the ORA-04091 is the EXCEPTION handling - you can't do anything to the table the trigger is attached to. In this case - PO_DISTRIBUTIONS_ALL

Reference: ORA-04091 Error

I suggest you encapsulate the logic in a stored procedure, and call it in the INSERT/UPDATE stored procedures prior to the INSERT/UPDATE statements.

Answer 2

The mutating table error means that trigger code may not issue DML statements on its owning table. This is to avoid recursive behaviour. The solution is quite simple. Remove the UPDATE statement.

The intention behind this statement is not quite clear. This is quite common, as the ORA-4091 error usually indicates poor design such as an insufficiently normalised data model. I think this is your situation. Consequently, resolving the problem requires a lot more business knowledge than we have.

It also appears that your SELECT statement is failing (the error stack as posted is a bit confused). You are attempting to suppress the failure of that statement with the notorious WHEN OTHERS THEN NULL; construct. This is Teh Suck! Remove it immediately. You need to know that your code is failing, and why. If the population of :NEW.Deliver_To_Location_Id is optional, so you don't mind if the lookup "fails" replace OTHERS with NO_DATA_FOUND.

Answer 3

The root cause of this problem is the use of triggers to do anything other than logging or other non-application related tasks. It's pretty widely regarded as very bad practice to use them for data manipulation in this way.

Adding to this is the use of the EXCEPTIONS clause to silently trap errors and invoke that update. And using WHEN OTHERS THEN NULL is a tragedy waiting to happen.

All terrible PL/SQL programming practices that should never have passed a code review.

Refactoring time.

Answer 4

As mentioned in earlier answers, you are getting mutating error becasue you are updating same table within trigger which is not allowed.

Since you are using "before update and insert" trigger, all you need to do is set :new.deliver_to_location_id with correct value. Since you are already doing that in select statement, to fix your trigger just remove the update statement, as your select statement is already updating the record field.

As an enhancement, you can replace "when others" exception with "when no_data_found" this will help you to throw all other unexpected errors.