Table is mutating, trigger/function may not see it

If you write a trigger to act on the same table, we could end up with the following error.

table TRANSACTION is mutating, trigger/function may not see it

This is due to the trigger is trying to act on the record which is getting inserted.

So if use an AFTER INSERT trigger, it will always fail.

Instead we can use BEFORE INSERT construct as shown below.

Here the source application trying to insert a record with status value either PROCESSED or INPROCESS

But if you have a requirement to get the status as 'NOT PROCESSED' for your application to process that record, we can update the source record before it commits to DB

================ Trigger Start=============================

create or replace
TRIGGER "ORAUSER"."TRANSACTION_INSERT"
BEFORE INSERT
ON ORAUSER.TRANSACTION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_event_seq NUMBER;
s_error VARCHAR2(256);

BEGIN

   IF :new.STATUS ='PROCESSED' OR :new.STATUS ='INPROCESS' THEN
           :new.STATUS :='NOT PROCESSED';
END IF;
 
   EXCEPTION
     WHEN OTHERS THEN
           BEGIN
   
s_error := SUBSTR(TO_CHAR(SQLCODE)||' '||SQLERRM,1,256);  
SELECT event_seq.NEXTVAL
INTO v_event_seq
FROM dual;  
INSERT INTO ERROR_TABLE
        VALUES (v_event_seq,s_error,:new.TRANSACTION_ID,'ERROR','TRANSACTION_INSERT',1,SYSDATE,0,NULL);
             
      END;
END TRANSACTION_INSERT;



=================Trigger End==============================