绕过Oracle中的突变触发器

Question

我有两个表;在更新表1(购买的汽车)时，我需要用特定客户购买的汽车总数更新另一个表。

当我尝试时，我遇到了一个突变的触发器。我曾试图将其转换为复合触发器，但我遇到了大量的错误，空索引等。

detail_table:

+-----------+---------+--------+------+------+
| customore | car     | number | cost | sold |
+-----------+---------+--------+------+------+
| josh      | mustang | 2      | 5    | y    |
| josh      | ford    | 3      | 2    | y    |
| josh      | tesla   | 1      | 3    | n    | -->to update to y
| john      | chevy   | 4      | 1    | y    |
| john      | chevy   | 5      | 2    | y    |
+-----------+---------+--------+------+------+

当从n更新到y时，行必须上卷并求和到这个汇总表

summary_table

+----------+------------+------------+
| customer | total cars | total cost | 
+----------+------------+------------+
| josh     | 5          | 7          | -- > before update  on detail
+----------+------------+------------+
+----------+------------+------------+
| customer | total cars | total cost | 
+----------+------------+------------+
| josh     | 6          | 10         | -- > after update on detail
+----------+------------+------------+

最后当用户将josh的n更新为y时。汽车总数为1，总成本为10

触发代码

CREATE OR REPLACE TRIGGER update_summary_table
AFTER UPDATE ON detail_table FOR EACH ROW 
referencing old as old new as new
begin

for i in (select sum(number) s_car, sum(cost) s_cost
from detail_table 
where  customer = :new.customer
and sold = 'y') loop
update summary_table
set total_cars = i.s_car,
total_cost = i.s_cost
where customer = :new.customer;
end loop;
end;
end update_summary_table;

Answer 1

为什么要使用for循环呢?只需使用一个更新:

update summary_table
set total_cars = coalesce(total_cars, 0) + :new.number - :old.number,
total_cost = coalesce(total_cost, 0) + :new.cost - :old.cost
where customer = :new.customer;

这对摘要进行增量更改，而不是完全重新计算行。

coalesce()的使用只是在新行没有缺省值的情况下。