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.
Best Answer
HOLIDAY_RESERVATION
, which is aliased asR
in yourUPDATE
statement , is not in theFROM
clause in theSELECT
query.