0

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;
babut1
  • 21
  • 2
  • 2
    PL/SQL means "Oracle" (as it is procedural extension to its SQL). However, "Unable to resolve column" doesn't sound Oracle-ish to me. So: which database do you use? Would you mind posting exact error you got (with error code and error message as database reported it, not using your own interpretation)? – Littlefoot Mar 31 '22 at 06:15
  • Little tip - use `varchar2` not `char` https://stackoverflow.com/a/42165653/230471 – William Robertson Mar 31 '22 at 22:02

1 Answers1

0

  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;
/
Sameh
  • 1,318
  • 11
  • 11
  • It does compile when I remove the colon before new. I have already tried simplified version of the code, still does not work for me. – babut1 Mar 31 '22 at 04:03
  • 1
    Adjust your question to include the DDL of the history table along with how are you conducting your test – Sameh Mar 31 '22 at 13:07