Error with Oracle trigger, invalid identifier

Question

I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working.

DML statement

UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = 
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0) +
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0)* NVL(R.OUT_FLIGHT_SEATS_NO,0) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);

Trigger statement

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
BEFORE INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
   SELECT 
      NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = :NEW.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0) +
      NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = :NEW.OUT_FLIGHT_ID), 0) * NVL(R.OUT_FLIGHT_SEATS_NO,0) +
      NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = :NEW.ACC_ID), 0)
      INTO :NEW.SUBTOTAL
   FROM DUAL;
END;


Errors for TRIGGER HR_SUBTOTAL:

LINE/COL ERROR
-------- ----------------------------------------------------------------
2/4      PL/SQL: SQL Statement ignored
4/92     PL/SQL: ORA-00904: "R"."OUT_FLIGHT_SEATS_NO": invalid identifier

The error has to be in the * multiplying bit, because if i get rid of multiplying bit, it works maybe it has to be enclosed in parenthesis or something with another select.

Well i got rid of both R's , but is still giving the same error — David Garcia
– David Garcia, Commented Mar 26, 2012 at 9:18
PL/SQL: SQL Statement ignored PL/SQL: ORA-00904: "OUT_FLIGHT_SEATS_NO": invalid identifier — David Garcia
– David Garcia, Commented Mar 26, 2012 at 9:21
change R. to :NEW. in trigger code. Then it will be equivalent to the update — turbanoff
– turbanoff, Commented Mar 26, 2012 at 9:23

GuZzie · Accepted Answer · 2012-03-26 09:21:00Z

4

There is no alias set for R in the query.

You should use the :NEW or :OLD option in the select statement as following

SELECT 
  NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = :NEW.IN_FLIGHT_ID), 0) * NVL(:OLD.IN_FLIGHT_SEATS_NO,0) +
  NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = :NEW.OUT_FLIGHT_ID), 0) * NVL(:OLD.OUT_FLIGHT_SEATS_NO,0) +
  NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = :NEW.ACC_ID), 0)
  INTO :NEW.SUBTOTAL
FROM DUAL;

answered Mar 26, 2012 at 9:21

GuZzie

9741 gold badge6 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error with Oracle trigger, invalid identifier

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related