mutating table error while firing trigger

Question

I receive error ORA-04091 while this trigger is fired:

    CREATE OR REPLACE TRIGGER SendNotifications 
       AFTER UPDATE OF penalty_amount ON Penalites
       FOR EACH ROW
   DECLARE
      phone_no number(9,0);
       BEGIN  
          SELECT PHONE_NUMBER INTO phone_no
            FROM ADRESSES
            INNER JOIN ORDERS
              ON adresses.ID_READER = orders.ID_READER
            INNER JOIN PENALITES
              ON orders.ID_ORDER = penalites.ID_ORDER
          WHERE :new.ISPAID = 'N';

          DBMS_OUTPUT.PUT_LINE('Phone numbers where selected.');
       END;
    /

I guess I should use :new. and :old. but I have no idea how to deal with it. Could you give me any suggestions or starting points?

What are you trying to accomplish? First, you can't have a `select` statement unless you are selecting data into some local variable or opening a cursor. A trigger can't return anything to the caller so you can't open and return a `sys_refcursor`. If you want to write a cursor loop and you only want to process rows related to the `penalty` row that is being modified, you could remove the join to `penalties` and use `orders.id_order = :new.id_order` but I'm not sure that is what you really want. — Justin Cave, Dec 06 '16 at 23:15
I want to select this into local variable. I updated my post. — monterinio, Dec 06 '16 at 23:25

score 0 · Answer 1 · edited May 23 '17 at 12:08

The SELECT statement in your trigger is attempting to retrieve data from PENALTIES, the table on which the trigger is defined. Row triggers such as this one are not allowed to retrieve data from the table upon which they're defined. You have three options:

Make this a statement trigger by removing the FOR EACH ROW, or
Remove PENALTIES from the SELECT statement. It appears that this statement would do the equivalent:

SELECT PHONE_NUMBER FROM ADRESSES INNER JOIN ORDERS ON adresses.ID_READER = orders.ID_READER WHERE :new.ISPAID = 'N' AND orders.ID_ORDER = :new.ID_ORDER;
Convert this to a compound trigger. See this answer for details

Best of luck.

It seems that the second option is the fastest one but I will check also the rest. Thank you. — monterinio, Dec 06 '16 at 23:31

mutating table error while firing trigger

1 Answers1