SQL Error: ORA-04091 while executing Trigger

0
votes

I am trying to execute my trigger:

CREATE OR REPLACE TRIGGER Trg_video_bfr_delete
    AFTER DELETE ON CMS_VIDEO
    FOR EACH ROW
DECLARE
    CODE varchar2(60);   
BEGIN
    SELECT CODE INTO CODE 
    from CMS_VIDEO 
    WHERE CODE = :OLD.CODE;
    IF CODE IS NOT NULL THEN
        INSERT INTO ASSET_DELETE_INDEX(CODE,ASSET_TYPE,IW_VPATH,LANGUAGE,MODIFIED_DTE) 
        VALUES (CODE,'Video',:old.IW_VPATH,:old.CONTENT_LANGUAGE,sysdate);
    END IF;
EXCEPTION 
    WHEN NO_DATA_FOUND THEN
        INSERT INTO ASSET_DELETE_INDEX (CODE,ASSET_TYPE,IW_VPATH,LANGUAGE,MODIFIED_DTE)
        VALUES (CODE,'Video',null,:old.CONTENT_LANGUAGE,sysdate);
        COMMIT;
END Trg_video_bfr_delete;
/

But I am getting the following error while executing a delete command on the table

Error report -

SQL Error: ORA-04091: table LSDS.CMS_VIDEO is mutating, trigger/function may not see it
ORA-06512: at "LSDS.TRG_VIDEO_BFR_DELETE", line 6
ORA-04088: error during execution of trigger 'LSDS.TRG_VIDEO_BFR_DELETE'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

Could anyone please help?

3
oracleplsqltriggers
I can't find the best duplicate, however, this is caused by you selecting from CMS_VIDEO in the trigger where you're deleting from that table. There's no need for you to actually do the SELECT, use :old.code directly... - Ben
I'd also avoid committing in a trigger (unless it's an autonomous transaction - another solution). If you subsequently want to rollback your transaction you can't. - Ben
Thanks Ben, using :old.code directly worked, but does my doubt is, does it work for deleting more than just one row? - Dhirish
You're not deleting anything in your trigger, you're using a row level, not a statement level, trigger and you were previously doing a select into ..., i.e. a scalar variable - there was only one row. There are no potential issues. - Ben
If you change the trigger to eliminate the SELECT...FROM CMS_VIDEO... and instead just use the value of :OLD.CODE it will work fine for multi-row deletes. That's what the FOR EACH ROW does - it causes the trigger to be what is known as a "row trigger", which is invoked for each and every row deleted. If you leave out the FOR EACH ROW then you have what is known as a "statement trigger", where the trigger is only invoked once for each triggering statement. - Bob Jarvis - Reinstate Monica

3 Answers

2
votes

You have written trigger AFTER DELETE ON CMS_VIDEO. This trigger will fire when DELETE is performed from CMS_VIDEO table. So when CMS_VIDEO table is being modified, you cannot modify or query the same table in any of your triggers, procedures or functions.

CREATE OR REPLACE TRIGGER Trg_video_bfr_delete
AFTER DELETE ON CMS_VIDEO
FOR EACH ROW

BEGIN
  IF :old.CODE IS NOT NULL THEN
    INSERT INTO ASSET_DELETE_INDEX(CODE, ASSET_TYPE, IW_VPATH, LANGUAGE, MODIFIED_DTE)
    VALUES (:old.CODE, 'Video', :old.IW_VPATH, :old.CONTENT_LANGUAGE, sysdate);
  END IF;
END Trg_video_bfr_delete;
/
0
votes

This is because you are deleting record from table and same table is used as reference inside trigger. This is case of "Mutating Error". You should switch to statement level trigger is it fits your requirement or you can use compound trigger which has multiple entry point and are capable of handling such scenarios.

Please refer https://oracle-base.com/articles/9i/mutating-table-exceptions

-1
votes

As per the snippet provided, I dont see any point in doing a SELECT on mutating TABLE. We can easily ise :OLD.CODE to fetch the value. Hope below snippet helps.

CREATE OR REPLACE TRIGGER Trg_video_bfr_delete AFTER
  DELETE ON CMS_VIDEO FOR EACH ROW 
--  DECLARE CODE VARCHAR2(60);
  BEGIN
--    SELECT CODE INTO CODE FROM CMS_VIDEO WHERE CODE = :OLD.CODE;
    IF CODE IS NOT NULL THEN
      INSERT
      INTO ASSET_DELETE_INDEX
        (
          CODE,
          ASSET_TYPE,
          IW_VPATH,
          LANGUAGE,
          MODIFIED_DTE
        )
        VALUES
        (
          :old.code,
          'Video',
          :old.IW_VPATH,
          :old.CONTENT_LANGUAGE,
          sysdate
        );
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
    INSERT
    INTO ASSET_DELETE_INDEX
      (
        CODE,
        ASSET_TYPE,
        IW_VPATH,
        LANGUAGE,
        MODIFIED_DTE
      )
      VALUES
      (
        :old.code,
        'Video',
        NULL,
        :old.CONTENT_LANGUAGE,
        sysdate
      );
    COMMIT;
  END Trg_video_bfr_delete;