Table is mutating, trigger/function may not see it ORA-06512

Question

I have two tables called DetailRental and Video. VID_NUM is the PK of Video and the FK of DetailRental.

What this code wants to achieve is when the Detail_Returndate or Detail_Duedate from DetailRental table changes(update or insert new row), the trigger will check the value of Detail_Returndate row by row. If its value is null, then the corresponding(according to VID_NUM) attribute VID_STATUS from Video table will change to "OUT".

The trigger has been created successfully. However, when I want to update the date. Oracle gives me error:

ORA-04091: table SYSTEM2.DETAILRENTAL is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM2.TRG_VIDEORENTAL_UP", line 3
ORA-04088: error during execution of trigger 'SYSTEM2.TRG_VIDEORENTAL_UP'

1. UPDATE DETAILRENTAL
2. SET DETAIL_RETURNDATE = null
3. WHERE RENT_NUM = 1006 AND VID_NUM = 61367

Below is my code:

CREATE OR REPLACE TRIGGER trg_videorental_up
  AFTER INSERT OR UPDATE OF DETAIL_RETURNDATE, DETAIL_DUEDATE ON DETAILRENTAL
  FOR EACH ROW
AS
  DECLARE
    DTRD DATE;
  BEGIN
    SELECT DETAIL_RETURNDATE
      INTO DTRD
      FROM DETAILRENTAL;
    IF DTRD IS NULL
      THEN UPDATE VIDEO
        SET VIDEO.VID_STATUS = 'OUT'
        WHERE EXISTS
          (SELECT DETAILRENTAL.VID_NUM
            FROM DETAILRENTAL
            WHERE DETAILRENTAL.VID_NUM = VIDEO.VID_NUM
          );
    END IF;
END;

Thank you very much!

problem solved here is the code:

CREATE OR REPLACE TRIGGER trg_videorental_up
AFTER INSERT OR UPDATE OF DETAIL_RETURNDATE, DETAIL_DUEDATE ON DETAILRENTAL
FOR EACH ROW
DECLARE DETAIL_RETURNDATE DATE;
BEGIN
IF :NEW.DETAIL_RETURNDATE IS NULL THEN UPDATE VIDEO SET VID_STATUS = 'OUT' WHERE VID_NUM = :NEW.VID_NUM;
ELSIF :NEW.DETAIL_RETURNDATE > SYSDATE THEN UPDATE VIDEO SET VID_STATUS = 'OUT' WHERE VID_NUM = :NEW.VID_NUM;
ELSIF :NEW.DETAIL_RETURNDATE <= SYSDATE AND TO_CHAR(DETAIL_RETURNDATE)!= '01/01/0001' THEN UPDATE VIDEO SET VID_STATUS = 'IN' WHERE VID_NUM = :NEW.VID_NUM;
ELSIF :NEW.DETAIL_RETURNDATE = '01/01/0001' THEN UPDATE VIDEO SET VID_STATUS = 'LOST' WHERE VID_NUM = :NEW.VID_NUM;
END IF;
END;

The question might be duplicate, but Mutating Triggers in Oracle occur if the wind blows the wrong way. Triggers, not sure why they named them so because they are not for event driven programming. Sure you can update your "updated_by" columns - but anything more and it's a piece of dung compared to Microsoft SQL Server. — Rich Bianco, Apr 12 '17 at 02:07

score 2 · Answer 1 · answered Nov 22 '15 at 04:44

A good data model is one in which no redundant information is physically stored. If you can look at one (or more) values in a table.column and figure out what value should be in another table.column, then you've got a redundancy. In your case, a person can see a DETAILRENTAL.DETAIL_DUEDATE for VIDNUM 61367 is not null, and "know" that the VIDEO.STATUS field should be OUT.

Most easily fixed with something like:
1) Create a VIDEO_BASE table, with all VIDEO columns except VID_STATUS:

CREATE TABLE VIDEO_BASE AS 
SELECT {list all columns except STATUS}
FROM VIDEO;

2) Drop original VIDEO table and create as a view, VIDEO, which shows all columns of VIDEO_BASE, plus exposes STATUS as a derived field:

CREATE OR REPLACE VIEW VIDEO 
AS 
SELECT  V.*,
        CASE WHEN 
            (
            SELECT COUNT(*) 
            FROM
                (
                SELECT 'X'
                FROM DETAILRENTAL D
                WHERE D.VID_NUM = V.VID_NUM 
                      AND DETAIL_RETURNDATE IS NOT NULL
                      AND ROWNUM <= 1
                )
            ) > 0 
        THEN 'OUT' 
        ELSE NULL
        END VID_STATUS
FROM VIDEO_BASE V;

In general, if you feel you need a trigger to keep two different tables in sync, you've got a data model problem. In my 15+ years experience with Oracle, the only best way to fix problematic triggers is to fix the data model - the surest way to know that all your triggers are working properly is when the number of triggers in your database is 0.

score 0 · Answer 2 · answered Nov 22 '15 at 07:37

After reading through @KevinKirkpatrick's answer two or three times, I realise he's right - an individual video's in/out status is derivable from other information in the database. That said, you may have pragmatic reasons for doing it this way.

The bad news is that you can't select from a table within a row trigger on that same table - that's what the "mutating table" problem means. The good news is that in this case you don't really need to.

I don't have an Oracle installation I can test this on, so I make no guarantee of syntactic correctness, but it should be close enough to get you started.

CREATE OR REPLACE TRIGGER trg_videorental_up
  AFTER INSERT OR UPDATE
    OF detail_duedate, detail_returndate
  ON detailrental
  FOR EACH ROW
AS
BEGIN
  IF :new.detail_returndate IS NULL
    AND :new.detail_duedate IS NOT NULL
  THEN
    UPDATE video
      SET status = 'OUT'
      WHERE video_num = :new.video_num;
  END IF;
END;

Table is mutating, trigger/function may not see it ORA-06512

2 Answers2