Oracle在更新前触发触发器

Question

我有桌子product(id, product_name, value, old_value,...)

在提升产品的价值之前，我想启动一个触发器来保存旧的价值。为此，我创建了以下触发器：

CREATE or REPLACE TRIGGER SET_OLDVALUE BEFORE update ON product
    FOR EACH ROW
        begin
             :new.OLD_VALUE = :old.VALUE;
        end;

但是，当我试图更新产品表时：

update product set value='newVal' where product_name='prodName';

我知道这个错误：

 [Error Code: 4098, SQL State: 42000]  ORA-04098: trigger 'product.SET_OLDVALUE' is invalid and failed re-validation

Answer 1

我认为它是无效的，因为您缺少冒号(:)

尝尝这个

         :new.OLD_VALUE := :old.VALUE;

create table product (
id      integer,
value   varchar2(10),
old_value varchar2(10)
);

CREATE or REPLACE TRIGGER SET_OLDVALUE BEFORE update ON product
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
    begin
         :new.OLD_VALUE := :old.VALUE;
    end;

insert into product (id, value) values (1, 'SHRIRAM');

commit;

update product
set value = 'NEW NAME'
where id = 1;

commit;

select *
  from product;

1  | NEW NAME | SHRIRAM