1

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.

3
  • HOLIDAY_RESERVATION, which is aliased as R in your UPDATE statement , is not in the FROM clause in the SELECT query.
    – Philᵀᴹ
    Mar 26, 2012 at 9:29
  • indeed i have set it to :OLD. thanks for noticing Mar 26, 2012 at 9:35
  • I'll add it as an answer
    – Philᵀᴹ
    Mar 26, 2012 at 10:05

1 Answer 1

5

HOLIDAY_RESERVATION, which is aliased as R in your UPDATE statement , is not in the FROM clause in the SELECT query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.