ORA-04084: cannot change NEW values for this trigger type

12,677

As requested in comments I'm making my comment as an answer.

Your problem is because you are trying to change a value AFTER the value was persisted, try changing your trigger to BEFORE as:

CREATE OR REPLACE TRIGGER TOTAL
  BEFORE UPDATE OR INSERT ON ORDER_ITEMS
  FOR EACH ROW
DECLARE
  temp  NUMBER;
  today DATE;
BEGIN
    temp:=(:NEW.item_price-:NEW.discount_amount)*:NEW.quantity;
    today := CURRENT_DATE;
    :NEW.TOTAL := temp;
    dbms_output.put_line('Updated on:' || today || ' item number: '
                           || :NEW.item_id || 'order number:' || :NEW.order_id 
                           || 'total: ' ||:NEW.total);
END;
/
Share:
12,677
RD7
Author by

RD7

Updated on June 09, 2022

Comments

  • RD7
    RD7 almost 2 years

    I'm trying to turn pl/sql trigger that calculates the total of some cells in the table when the tale is changed. This is the code:

      ALTER session SET nls_date_format='dd/mm/yyyy';
    
      CREATE OR REPLACE TRIGGER TOTAL
      AFTER UPDATE OR INSERT ON ORDER_ITEMS
      FOR EACH ROW
        DECLARE
    temp  NUMBER;
    today DATE;
      BEGIN
             temp:=(:NEW.item_price-:NEW.discount_amount)*:NEW.quantity;
             today := CURRENT_DATE;
            :NEW.TOTAL := temp;
              dbms_output.put_line('Updated on:' ||today || ' item number: ' ||:NEW.item_id|| 'order number:' ||:NEW.order_id|| 'total: ' ||:NEW.total);
      END;
      /
      show errors
    
      insert into order_items (ITEM_ID, ORDER_ID, PRODUCT_ID, ITEM_PRICE, discount_amount, QUANTITY)
      VALUES (13, 7, 3, 553, 209, 2);
    

    And I get this error:

    1. 00000 - "cannot change NEW values for this trigger type" *Cause: New trigger variables can only be changed in before row insert or update triggers. *Action: Change the trigger type or remove the variable reference. No Errors. 1 rows inserted Updated on:06/01/2016 item number: 13order number:7total:

    I understand that the problem is updating a table during the trigger execution caused by an update to the same table.