ORA-04084:无法更改此触发类型的新值 [英] ORA-04084: cannot change NEW values for this trigger type
本文介绍了ORA-04084:无法更改此触发类型的新值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用pl/sql触发器,该触发器会在更改故事后计算表中某些单元格的总数.这是代码:
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);
我收到此错误:
- 00000-无法更改此触发类型的新值" *原因:新的触发变量只能在行前更改 插入或更新触发器. *操作:更改触发器类型或删除变量引用.没有错误.插入1行更新于:06/01/2016产品编号:13order 数:7共:
- 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.
您的问题是因为您要在持久化该值之后尝试更改值,请尝试将触发器更改为BEFORE
:
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;
/
这篇关于ORA-04084:无法更改此触发类型的新值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文