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 '12 at 9:29
  • indeed i have set it to :OLD. thanks for noticing – David Garcia Mar 26 '12 at 9:35
  • I'll add it as an answer – Philᵀᴹ Mar 26 '12 at 10:05
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, privacy policy and cookie policy

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