I’m attempting to create a trigger in Oracle to maintain a log of changes in a specific table. My goal is to record inserts, updates, and deletes in the CLIENTS table into an audit table called CLIENTS_LOG. However, when trying to create the trigger, I encounter errors related to the NEW and OLD bind variables. It seems like the link variable is wrong.

Here’s the trigger code I’m using:

CREATE OR REPLACE TRIGGER CLIENTS_LOG_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON CLIENTS
FOR EACH ROW
DECLARE
V_OLD_NAME VARCHAR2(100);
V_OLD_LASTNAME VARCHAR2(100);
BEGIN
IF INSERTING THEN
INSERT INTO CLIENTS_LOG (AUDIT_ID, CLIENT_ID, NEW_NAME, NEW_LASTNAME, MODIFICATION_DATE, MODIFYING_USER)
VALUES (AUDIT_SEQ.NEXTVAL, :NEW.CLIENT_ID, :NEW.NAME, :NEW.LASTNAME, SYSTIMESTAMP, USER);
ELSIF UPDATING THEN
SELECT NAME, LASTNAME INTO V_OLD_NAME, V_OLD_LASTNAME FROM CLIENTS WHERE CLIENT_ID = :OLD.CLIENT_ID;
INSERT INTO CLIENTS_LOG (AUDIT_ID, CLIENT_ID, OLD_NAME, NEW_NAME, OLD_LASTNAME, NEW_LASTNAME, MODIFICATION_DATE, MODIFYING_USER)
VALUES (AUDIT_SEQ.NEXTVAL, :OLD.CLIENT_ID, V_OLD_NAME, :NEW.NAME, V_OLD_LASTNAME, :NEW.LASTNAME, SYSTIMESTAMP, USER);
ELSIF DELETING THEN
INSERT INTO CLIENTS_LOG (AUDIT_ID, CLIENT_ID, OLD_NAME, OLD_LASTNAME, MODIFICATION_DATE, MODIFYING_USER)
VALUES (AUDIT_SEQ.NEXTVAL, :OLD.CLIENT_ID, :OLD.NAME, :OLD.LASTNAME, SYSTIMESTAMP, USER);
END IF;
END;

Also, here are the tables:

— Table structure for CLIENTS
CREATE TABLE CLIENTS (
CLIENT_ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100),
LASTNAME VARCHAR2(100)
);

— Table structure for CLIENTS_LOG
CREATE TABLE CLIENTS_LOG (
AUDIT_ID NUMBER PRIMARY KEY,
CLIENT_ID NUMBER,
OLD_NAME VARCHAR2(100),
NEW_NAME VARCHAR2(100),
OLD_LASTNAME VARCHAR2(100),
NEW_LASTNAME VARCHAR2(100),
MODIFICATION_DATE TIMESTAMP,
MODIFYING_USER VARCHAR2(100)
);

I have tried to change the log table or modify the :OLD and :NEW clauses but i keep running into the same problem.