我想在我的程序中实现的一个触发器出了问题。
我有一张名为“产品”的表格,里面列有ID、价格和折扣。
我做了这个扳机
CREATE or REPLACE TRIGGER DISCOUNT_TRIGGER
after update of DISCOUNT on PRODUCTS
for each row
BEGIN
update PRODUCTS set PRICE = PRICE * (1 - DISCOUNT);
END;我想在更新折扣栏后更改行的价格列。
例如,如果我执行此查询:
UPDATE PRODUCTS SET DISCOUNT = 25 WHERE ID = 25;当我执行该查询时,将得到以下错误:
Error starting at line : 11 in command -
update products set DISCOUNT = 25 where id = 1
Error report -
ORA-04091: table YASSINEII.PRODUCTS is mutating, trigger/function may not see it
ORA-06512: at "YASSINEII.DISCOUNT_TRIGGER", line 2
ORA-04088: error during execution of trigger 'YASSINEII.DISCOUNT_TRIGGER'你知道我在这里做错了什么吗?
发布于 2018-10-11 19:42:21
我猜你真的想要一个“更新前”触发器来更新单个行中的值。类似于:
CREATE or REPLACE TRIGGER DISCOUNT_TRIGGER
before update of DISCOUNT on PRODUCTS
for each row
BEGIN
:new.price := :new.price * ( 1 - :new.korting_percentage);
END;发布于 2018-10-11 19:53:07
无法查询导致触发器在触发器内部触发的表。
但在这种情况下,我不认为有任何必要。可以使用:new来访问从表中选择的值
CREATE or REPLACE TRIGGER DISCOUNT_TRIGGER
after update of DISCOUNT on PRODUCTS
for each row
BEGIN
update PRODUCTS set :new.PRICE = :new.PRICE * (1 - korting_percentage);
END;发布于 2018-10-11 20:10:27
可以避免使用复合触发器更改表异常。
在这里,示例(请记住更改表和列以适合您的数据模型):
CREATE or REPLACE TRIGGER DISCOUNT_TRIGGER
FOR UPDATE of DISCOUNT on PRODUCTS
COMPOUND TRIGGER
BEFORE STATEMENT is
BEGIN
UPDATE your_table SET your_column = your_value WHERE your_condition;
END BEFORE STATEMENT;
--BEFORE EACH ROW is
--BEGIN
--Put here the statement
--END BEFORE EACH ROW;
--AFTER STATEMENT is
--BEGIN
--Put here the statement
--END AFTER STATEMENT;
--AFTER EACH ROW is
--BEGIN
--Put here the statement
--END AFTER EACH ROW;
END;https://stackoverflow.com/questions/52767717
复制相似问题