I want two values, in two different tables, to be used as a total in a third table.
- Add value from
acc_pricewith value from
- Add those values up to be used in table reservation
Things I've tried;
SELECT F.FLI_PRICE + AC.ACC_PRICEPN INTO R.SUBTOTAL FROM HOLIDAY_RESERVATION R, FLIGHT F, ACCOMMODATION AC;
I wish to add the code from Leigh into a trigger, I tried the following but got a mutation error
CREATE OR REPLACE TRIGGER HR_SUBTOTAL AFTER DELETE OR INSERT OR UPDATE ON HOLIDAY_RESERVATION FOR EACH ROW BEGIN UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) + NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0) + NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0); END; / ERROR at line 1: ORA-04091: table LATIN.HOLIDAY_RESERVATION is mutating, trigger/f ORA-06512: at "LATIN.HR_SUBTOTAL", line 2 ORA-04088: error during execution of trigger 'LATIN.HR_SUBTOTAL'