Reputation: 2276
I created this trigger:
CREATE or REPLACE TRIGGER My_TRIGGER
AFTER UPDATE ON product
REFERENCING NEW AS NEW
OLD AS OLD
FOR EACH ROW
BEGIN
IF (:NEW.name <> :OLD.name)
THEN
INSERT INTO product_history
(
ID,
PRODUCT_ID,
NAME
)
VALUES
(
seq_product.nextval,
:OLD.PRODUCT_ID,
:OLD.NAME
);
END IF;
END;
when I update the table product, I get this error:
[Error Code: 4098, SQL State: 42000] ORA-04098: trigger 'My_TRIGGER' is invalid and failed re-validation
I execute this query:
select * from user_errors where type = 'TRIGGER' ;
I get:
PL/SQL: ORA-00904: "NAME": invalid identifier
I can't find the issue?!
Upvotes: 0
Views: 2225
Reputation: 95101
The clause
REFERENCING NEW AS NEW OLD AS OLD
exists so that you can write OLD.NAME
instead of :OLD.NAME
, but in your code you are still writing :OLD.NAME
, which should be the cause that NAME
is not known anymore.
I'd simply remove the referencing clause and everything should be fine.
Upvotes: 1