PL/SQL errors when using :new

Question

I am trying to write a simple trigger in PL/SQL that will insert some values into RESERVATIO_LOG table after a reservation is added. The problem is with :new. The errors are: Unable to resolve column 'Reservation_ID' etc. When I remove the colon (res_id := new.RESERVATION_ID) the errors disappear, yet the trigger does not work. What do I do wrong?

CREATE TABLE reservation (
RESERVATION_ID INT GENERATED ALWAYS AS IDENTITY NOT NULL , TRIP_ID INT
, PERSON_ID INT
, STATUS CHAR(1)
, NO_PLACES INT
, CONSTRAINT reservation_PK PRIMARY KEY (
RESERVATION_ID )
ENABLE );

CREATE OR REPLACE TRIGGER ADDING_RESERVATION
    AFTER INSERT OR UPDATE
    ON reservation
    FOR EACH ROW
DECLARE
    res_id INT;
    stat CHAR;
    no_places INT;
BEGIN
    res_id  := :new.RESERVATION_ID;
    stat := :new.STATUS;
    no_places := :new.NO_PLACES;
    INSERT INTO RESERVATION_LOG (RESERVATION_ID, CHANGE_DATE, STATUS, NO_PLACES)
    VALUES(res_id, trunc(SYSDATE), stat, no_places);
END;

Answer 1

1. Have you tried to check if the trigger code is compiled successively

2. Try this simpler version of trigger code

3. Reservation Log table should have another PK rather than RESERVATION_ID column , I would suggest to have an identity field History_Id

CREATE OR REPLACE TRIGGER ADDING_RESERVATION 
  AFTER INSERT OR UPDATE
  ON reservation
  FOR EACH ROW
  BEGIN
    INSERT INTO RESERVATION_LOG (RESERVATION_ID, CHANGE_DATE, 
     STATUS, NO_PLACES)
    VALUES(:new.RESERVATION_ID, current_date, :new.STATUS, 
    :new.NO_PLACES);
  END;
/