CODEWITHSUNDEEP
sqloracle-databasetriggersora-00913
David Garcia
David Garcia

Reputation: 2696

Oracle Trigger error PL/SQL: ORA-00913: too many values

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

Upvotes: 1

Views: 2871

Answers (2)

Ollie
Ollie

Reputation: 17548

Couldn't you just do something like this:

CREATE OR REPLACE TRIGGER INV_TOTAL
BEFORE INSERT OR UPDATE ON INVOICE
FOR EACH ROW
BEGIN
   -- Add 20% to subtotal and populate inv_total_price
   SELECT (NVL(r.subtotal, 0) * 1.2) -- Multiplying by 1.2 adds 20% to the total
     INTO :NEW.INV_TOTAL_PRICE
     FROM holiday_reservation r
    WHERE r.resv_id = :NEW.inv_id;
EXCEPTION
   WHEN no_data_found
   THEN
      -- Set the inv_total_price to zero as there was no corresponding 
      -- record in holiday_reservation.
      :NEW.INV_TOTAL_PRICE := 0;
END;
/

I do not have a terminal in front of me to test this at the moment though. :-(
N.B.:You may or may not want the exception section.
Hope it helps...

Upvotes: 1

Dmitry Reznik
Dmitry Reznik

Reputation: 6862

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.

Upvotes: 0

Related Questions