Monday, 27 April 2015

ORA-04091 table is mutating

Error:


Records Sucessfully Inserted. But when i tried to Update the same field with another PAN Number raising below error.

Cause:
A trigger or a user-defined PL/SQL function that is referenced in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger.

Solution:
Rewrite the trigger (or function) so it does not read that table.

Crated Trigger

CREATE OR REPLACE TRIGGER APPS.DUPLICATE_PAN
   BEFORE INSERT OR UPDATE
   ON JAI_AP_TDS_VENDOR_HDRS
   FOR EACH ROW
DECLARE
   A   NUMBER (10);
BEGIN
   SELECT COUNT (*)
     INTO A
     FROM JAI_AP_TDS_VENDOR_HDRS
    WHERE     PAN_NO = :NEW.PAN_NO
          --AND VENDOR_ID = :NEW.VENDOR_ID
          AND VENDOR_SITE_ID = :NEW.VENDOR_SITE_ID
          AND TDS_VENDOR_TYPE_LOOKUP_CODE = 'OTHERS-IND';

   IF A > 0
   THEN
      RAISE_APPLICATION_ERROR (-20454, 'PAN NUMBER ALREADY EXISTS');
   END IF;
END;