I am trying to create a trigger but is having errors....
SQL> CREATE OR REPLACE TRIGGER INV_TOTAL
2 BEFORE INSERT OR UPDATE ON INVOICE
3 FOR EACH ROW
4 BEGIN
5 SELECT
6 NVL((SELECT R.SUBTOTAL FROM HOLIDAY_RESERVATION R WHERE R.RESV_ID = :NEW.INV_ID), 0) +
7 NVL((SELECT R.SUBTOTAL, (R.SUBTOTAL*20)/100 FROM HOLIDAY_RESERVATION R WHERE R.RESV_ID = :NEW.RESV_ID),0)
8 INTO :NEW.INV_TOTAL_PRICE
9 FROM DUAL;
10 END;
11 /
Warning: Trigger created with compilation errors.
SQL> SHOW ERRORS;
Errors for TRIGGER INV_TOTAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4 PL/SQL: SQL Statement ignored
4/9 PL/SQL: ORA-00913: too many values
SQL>
Where is it finding too many values, all i want is select whats in field subtotal, add 20% and update a field in another table
The reason behind "Too many values" error is that in your select statements you do the following:
NVL((SELECT R.SUBTOTAL FROM HOLIDAY_RESERVATION R WHERE R.RESV_ID = :NEW.INV_ID), 0) ' 1 value
NVL((SELECT R.SUBTOTAL, (R.SUBTOTAL*20)/100 FROM HOLIDAY_RESERVATION R WHERE R.RESV_ID = :NEW.RESV_ID),0) ' gives 2 values (subtotal, subtotal*20 / 100)
Calling NVL with 2 values throws ORA-00913. Even if it didn't, it'd throw eventually when you try to add 1 value with 2.
Also, I'd set fully qualified schema names beside every declaration.