How to insert current datetime into a table in a trigger?

I've been working on a database that is has data stored about trips and reservations for these trips. I also have a table ReservationLog, which stores changes in status of a given resevation, and when they happenned. I am told to create a trigger, working after adding new reservation, which also creates a new entry in ReservationLog table. However, when trying to compile the code I get an error: PLS-00049 - incorrect binding variable "NEW.LOG_DATE"

The table ReservationLog looks like this:

CREATE TABLE  RESERVATIONLOG
(
    ID INT GENERATED ALWAYS  AS IDENTITY NOT NULL,
    RESERVATION_ID INT,
    LOG_DATE DATE,
    STATUS CHAR(1),
    CONSTRAINT RL_PK PRIMARY KEY
    (
        ID
    )
    ENABLE
);

RESERVATION_ID is a foreign key

The code for trigger function looks like it:

CREATE OR REPLACE TRIGGER AddReservationTrigger
    AFTER INSERT ON
    RESERVATION
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    DECLARE  V_TODAY DATE;

    BEGIN

        SELECT SYSDATE INTO V_TODAY FROM DUAL;
        INSERT INTO RESERVATIONLOG
            (
             RESERVATION_ID,
             LOG_DATE,
             STATUS
            )
            VALUES
            (
             :new.RESERVATION_ID,
             :new.LOG_DATE,
             :new.STATUS
);
    end;
/

Not sure what to do, I tried to assign a value to it: :new.LOG_DATE :=SYSDATE but it doesn't work. I feel like this is something stupid, but can't figure out what is wrong here.

CodePudding user response：

You can refer to things like sysdate directly in your insert statement, e.g.:

CREATE OR REPLACE TRIGGER AddReservationTrigger
AFTER INSERT ON RESERVATION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN

    INSERT INTO RESERVATIONLOG
        (
         RESERVATION_ID,
         LOG_DATE,
         STATUS
        )
        VALUES
        (
         :new.RESERVATION_ID,
         SYSDATE,
         :new.STATUS
        );
end;
/

Side note: the reason you can't change :new.LOG_DATE is because this is an "AFTER" trigger on the RESERVATION table; the trigger fires after the insert has already been completed, so it doesn't make sense to change the value of that column.