触发变异问题 [英] Trigger mutating issue

问题描述

我桌子上有多少辆不同的汽车，每辆汽车都有自己的价格. 每次更新或在表中插入新行时，我都需要重新计算并更新平均价格.

I have number of different cars in the table and each car has its own price. Every time something is gets updated or new row is inserted in table I need to recompute and update average price.

我试图在触发器内使用AVG()函数，但突然遇到运行时错误: ORA-04091: table abcde.SCL is mutating, trigger/function may not see it

I am trying to use AVG() function inside the Trigger but suddenly getting run time error: ORA-04091: table abcde.SCL is mutating, trigger/function may not see it

我认为解决方案是使用语句而不是行触发器触发器.在这种情况下，语句触发器不允许使用:new.ID or :old.ID，那么我如何检测哪个汽车品牌已更新，以便重新计算平均值?

I believe that solution is to use statement instead of row trigger trigger. In this case statements triggers do no allow to use :new.ID or :old.ID, then how do I detect which car brand is updated, in order to recompute an average?

create or replace trigger AvgTrigger
after insert on cars
for each row
declare     
    carAvg number;
begin   
    SELECT Avg(price) into carAvg from car where id# = :new.id#;    
end;

推荐答案

处理这种情况的一种方法是使用行级触发器在临时表中记录一些信息，例如已修改的id#，然后使用语句级触发器读取该信息并执行所需的工作.

One way to deal with this kind of situation is to use a row-level trigger to record some information, like the id# modified, in a temporary table, then use a statement-level trigger to read that information and perform the desired work.

但是，在这种情况下，我会质疑这是否完全值得.考虑在需要时计算查询中的平均值；或将其嵌入到Shannon评论中建议的视图中；或将其嵌入到物化视图中(如果即时进行计算会产生很大的开销).

However, in this case I would question whether that is worthwhile at all. Consider computing the average in your queries when it is needed; or embedding it in a view as suggested in Shannon's comment; or embedding it in materialized view if computing it on the fly is too much overhead.

这篇关于触发变异问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！