CODEWITHSUNDEEP
oracle-databasetriggers
senior
senior

Reputation: 2276

Oracle trigger: PL/SQL: ORA-00904: "Column": invalid identifier

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

Answers (1)

Thorsten Kettner
Thorsten Kettner

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

Related Questions