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:
- 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.
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;
/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With