samedi 21 novembre 2015

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

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
    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!

Aucun commentaire:

Enregistrer un commentaire