如何解决变异表中的错误(预言机)

Question

>我创建以下表：

create table products
(
        code             varchar(9),
        group_code       varchar(9),
        price            number,  
CONSTRAINT pk_code PRIMARY KEY (code)
);

我创建了一个触发器，为此每组产品的价格不超过 100 美元：

create or replace trigger nomore100
before insert or update on products
for each row
declare
cursor c_total is
select group_code, sum(price) as total_price
from products
where group_code=:new.group_code
group by group_code;
v_total c_total%rowtype;
begin
for v_total in c_total loop
    if v_total.total_price+:new.price > 100 then
        raise_application_error(-20150,'No more than 100 dollars');
    end if;
end loop;
end nomore100;
/

触发器适用于插入，但是当我尝试进行更新时：

update products set price=120 where code='PX1';

甲骨文返回：

"表 %s.%s 正在发生变化，触发器/函数可能看不到它"

感谢您的任何建议或答案，祝你有美好的一天！

Answer 1

您可以使用声明部分中的杂注autonomous_transaction来避免表变异错误。

宣编译指示autonomous_transaction;-- 添加此行以启用自主事务

开始法典结束;

Answer 2

Oracle 不允许您执行此类操作，因为它可能导致数据不一致或无限循环。

若要变通解决此问题，您可以使用复合触发器或前后触发器的组合。以下示例说明了如何使用触发器前后的组合来修改触发器：

-- Create a before update trigger to store the old price
CREATE OR REPLACE TRIGGER store_old_price
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  :old.price := :new.price;
END;
/
-- Create an after update trigger to enforce the price limit
CREATE OR REPLACE TRIGGER nomore100
AFTER UPDATE ON products
FOR EACH ROW
DECLARE
  v_total NUMBER;
BEGIN
  SELECT SUM(price) INTO v_total
  FROM products
  WHERE group_code = :new.group_code;
  IF v_total > 100 THEN
    raise_application_error(-20150, 'No more than 100 dollars');
  END IF;
END;
/

使用此方法，"store_old_price"触发器在更新之前捕获旧价格，"nomore100"触发器在更新后检查总价格以强制执行限制。这样，您可以避免更改表错误。

现在，当您执行更新语句时：

UPDATE products SET price = 120 WHERE code = 'PX1';

触发器应该可以正常工作，并且它们将确保每组的总价格不超过 100 美元。