delete code in trigger throwing up errors oracle

Question

i don't know how the code throws up errors. it compiles correctly but when it used it throws up an error.

create or replace TRIGGER TRG_APPLICATIONS
BEFORE INSERT or UPDATE OF APP_ID, STATUS_ID
ON APPLICATIONS
FOR EACH ROW
BEGIN

:new.APP_ID := SEQ_APP_ID.nextval;
:new.APP_DATE := SYSDATE;

IF UPDATING AND ( :NEW.STATUS_ID = 2 OR :NEW.STATUS_ID = 5 OR :NEW.STATUS_ID = 7 OR :NEW.STATUS_ID = 8 )
THEN
   INSERT INTO APP_HISTORY (SRN, STATUS_ID, APP_DATE)
   VALUES (:OLD.SRN, :OLD.STATUS_ID, :OLD.APP_DATE);       

       DELETE FROM APPLICATIONS    WHERE :NEW.STATUS_ID = 2;
       DELETE FROM APPLICATIONS    WHERE :NEW.STATUS_ID = 5;
       DELETE FROM APPLICATIONS    WHERE :NEW.STATUS_ID = 7;
       DELETE FROM APPLICATIONS    WHERE :NEW.STATUS_ID = 8;

END IF;

END;

this is the error message

error ORA-04091: table APEX514.APPLICATIONS is mutating, 
trigger/function may not see it ORA-06512: at "APEX514.TRG_APPLICATIONS", 
line 14 ORA-04088: error during execution of trigger 'APEX514.TRG_APPLICATIONS'

(this was the full error message error) error ORA-04091: table APEX514.APPLICATIONS is mutating, trigger/function may not see it ORA-06512: at "APEX514.TRG_APPLICATIONS", line 14 ORA-04088: error during execution of trigger 'APEX514.TRG_APPLICATIONS' — MrOnlyonejack
– MrOnlyonejack, Commented Oct 27, 2015 at 15:36
Please edit the error message into your question using the edit button. — Ross
– Ross, Commented Oct 27, 2015 at 15:41

Sabe · Accepted Answer · 2015-10-27 15:41:53Z

1

The most likely cause of a mutating table error is the misuse of triggers. Here is a typical example: 1.you insert a row in table A

2.a trigger on table A (for each row) executes a query on table A, for example to compute a summary column

3.Oracle throws an ORA-04091: table A is mutating, trigger/function may not see it

Source for further reading. http://dba.stackexchange.com/questions/5432/what-are-the-causes-and-solutions-for-mutating-table-errors

answered Oct 27, 2015 at 15:41

Sabe

1861 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sabe Over a year ago

A mutating table occurs when a statement causes a trigger to fire(UPDATE or INSERT in your case) and that trigger(DELETE) references the table that caused the trigger. You have two DMLs in one trigger that references the same table which causes an inconsistent state of data.

przemo_pl Over a year ago

the bottom line is that you should move the code to functions / procedures and not store it in a trigger... There is a solution to use compound trigger instead of 'regular' one but I would personally advise not to if possible :) here's the answer: stackoverflow.com/questions/32473980/…

Collectives™ on Stack Overflow

delete code in trigger throwing up errors oracle

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related