SQL Database trigger ORA-00904

Question

I am trying to enter customer name, start date and ship name into the audit table if the booking evaluation number is 2. I am getting an invalid identifier error when the name I am using is in the booking table. For now, I am just trying to get the customer name in the audit table so therefore got null in the other columns.

This is the code I have I cant see where I am going wrong:

CREATE OR REPLACE TRIGGER EVALUATION
BEFORE INSERT OR UPDATE OR DELETE ON BOOKING
FOR EACH ROW 
WHEN (NEW.BOOKING_EVALUATION = 2)
DECLARE

custname  VARCHAR(20);
sdate    DATE;
shname   VARCHAR(50);

BEGIN

SELECT CUSTOMER_NAME INTO custname FROM customer WHERE CUSTOMER_NAME = NEW.CUSTOMER_NAME;


IF INSERTING THEN
INSERT INTO EVALUATIONAUDIT (VOYAGES_ID,CUSTOMER_NAME,START_DATE,SHIP_NAME,BOOKING_EVALUATION)
VALUES(:NEW.VOYAGES_ID,custname,NULL,NULL,:NEW.BOOKING_EVALUATION);
END IF;


IF UPDATING THEN
INSERT INTO EVALUATIONAUDIT (VOYAGES_ID,CUSTOMER_NAME,START_DATE,SHIP_NAME,BOOKING_EVALUATION)
VALUES(:OLD.VOYAGES_ID,custname,NULL,NULL,:OLD.BOOKING_EVALUATION);
END IF;


IF DELETING THEN
INSERT INTO EVALUATIONAUDIT (VOYAGES_ID,CUSTOMER_NAME,START_DATE,SHIP_NAME,BOOKING_EVALUATION)
VALUES(:OLD.VOYAGES_ID,custname,NULL,NULL,:OLD.BOOKING_EVALUATION);
END IF;
END;
/

This is the error I am getting:

9/1 PL/SQL: SQL Statement ignored
9/67 PL/SQL: ORA-00904: "NEW"."CUSTOMER_NAME": invalid identifier

Answer 1

You need to use a field from the NEW pseudorecord, and since the table the trigger is against only has the ID, you need to use that for the look-up:

SELECT CUSTOMER_NAME
INTO custname
FROM customer
WHERE CUSTOMER_ID = :NEW.CUSTOMER_ID;

Notice the : prefix for NEW, as well as the column/field name change.

This won't quite work for the IF DELETING branch though, as the NEW pseudorecord will be empty in that case - only OLD is populated. So you need additional logic to get the customer name based on OLD or NEW depending on the triggering event, and based on you using OLD in the UPDATING branch you might want the old customer ID there too.

The WHEN (NEW.BOOKING_EVALUATION = 2) check won't work on delete either, for the same reason.

So you could maybe do:

CREATE OR REPLACE TRIGGER EVALUATION
BEFORE INSERT OR UPDATE OR DELETE ON BOOKING
FOR EACH ROW 
DECLARE

  custname  VARCHAR(20);

BEGIN

  IF INSERTING AND :NEW.BOOKING_EVALUATION = 2 THEN
    SELECT CUSTOMER_NAME
    INTO custname
    FROM customer
    WHERE CUSTOMER_ID = :NEW.CUSTOMER_ID;

    INSERT INTO EVALUATIONAUDIT (VOYAGES_ID,CUSTOMER_NAME,START_DATE,SHIP_NAME,BOOKING_EVALUATION)
    VALUES(:NEW.VOYAGES_ID,custname,NULL,NULL,:NEW.BOOKING_EVALUATION);
  END IF;

  IF (UPDATING OR DELETING) AND :OLD.BOOKING_EVALUATION = 2 THEN
    SELECT CUSTOMER_NAME
    INTO custname
    FROM customer
    WHERE CUSTOMER_ID = :OLD.CUSTOMER_ID;

    INSERT INTO EVALUATIONAUDIT (VOYAGES_ID,CUSTOMER_NAME,START_DATE,SHIP_NAME,BOOKING_EVALUATION)
    VALUES(:OLD.VOYAGES_ID,custname,NULL,NULL,:OLD.BOOKING_EVALUATION);
  END IF;
END;
/

depending on whether you want to record an update if the old, new or either BOOKING_EVALUATION is 2. This version only looks at OLDin the UPDATING OR DELETING as those are the other values you're using for the insert, but could easily check either, or you could split the UPDATING and DELETING out again. Or you could change the first check to:

...
BEGIN

  IF (INSERTING OR UPDATING) AND :NEW.BOOKING_EVALUATION = 2 THEN
...

It depends what scenarios you want to capture and what values you want to record for them.

---

If you get a compilation error then either run show errors or query the user_errors view to see the actual failures. If you get ORA-04098 then the trigger that error refers to is invalid - it either was when it was compiled or (less likely here I think) the table structure has changed since it last successfully compiled.

You may have created multiple triggers. Look at the output of

select trigger_name, status from user_triggers where table_name = 'BOOKING';

to see how many triggers there are against this table (though it could be elsewhere) and what they are called - if there is more than one then see if you have an earlier attempt with a different name that you need to drop.

And look at

select object_name, status from user_objects where object_type = 'TRIGGER';

to see all the triggers you have defined and look for any which are invalid. You can then go back to the user_triggers view to see which tables they are against if it isn't obvious from their names.

Whatever is invalid though, the reason will be visible in user_errors.