CODEWITHSUNDEEP
oracle-databaseplsqlsqlplusdatabase-trigger
J Nguyen
J Nguyen

Reputation: 147

After Insert Trigger ORA-01422: fetch returns more than requested number of rows

Can anyone help me with this code below. There are 3 tables : Customer_A1, Reservation_A1 and Invoice_A1. I am writing a trigger that will execute every time a new reservation is made.

The trigger will pre-loaded the invoice table the information of invoice_id (inv_id), reservation_id (res_id), customer first name (cust_fname), customer last name (cust_lname) and reservation_start_date.

My code is below. There is no compilation errors when trigger is created. However when i insert a new row to Reservation table which makes the trigger execute, it inform me of that my trigger has an error of

ORA-01422: fetch returns more than requested number of rows. 

CREATE OR REPLACE TRIGGER invoice_after_reservation_made
AFTER INSERT
ON RESERVATION_A1
FOR EACH ROW

DECLARE
inv_id INVOICE_A1.INV_ID%type;
res_id INVOICE_A1.res_id%type;
room_id INVOICE_A1.room_id%type;
cust_fname INVOICE_A1.cust_fname%type;
cust_lname INVOICE_A1.cust_lname%type;
reservation_start_date INVOICE_A1.reservation_start_date%type;
cust_id RESERVATION_A1.cust_id%type;

BEGIN
--read reservation_id
res_id:= :new.res_id;
--read room_id
room_id:= :new.room_id;
--read reservation_start_date
reservation_start_date:= :new.reservation_start_date;
--read customer_id
cust_id:= :new.cust_id;
--create new invoice_id
SELECT MAX(INVOICE_A1.inv_id)+1 INTO inv_id FROM INVOICE_A1;

-- import value from CUSTOMER_A1 table to variable cust_fname, cust_lname
Select CUSTOMER_A1.cust_fname,CUSTOMER_A1.cust_lname INTO 
cust_fname,cust_lname 
FROM CUSTOMER_A1 
WHERE CUSTOMER_A1.cust_id=cust_id;

-- Insert record into invoice table
INSERT INTO INVOICE_A1
VALUES (inv_id,res_id,room_id,cust_fname,cust_lname,null,TO_DATE(TO_CHAR(reservation_start_date),'DD/MM/YYYY'),null);


END;

Note: I have looked up for solution on internet however no cigar though. People said the problem mostly come from Select statements that return more than one row. However my Select query in the code above return only one row. I also check the table's data, No entity and referential integrity are violated in 3 tables Customer_A1, Reservation_A1 and Invoice_A1. I even copy a code to a separate test procedure to print out all variables after reading inputs. The test procedure work well. I surrender now. Please help me with this problem. I am new . Thanks

Upvotes: 1

Views: 963

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50037

The problem is in the statement

Select CUSTOMER_A1.cust_fname,CUSTOMER_A1.cust_lname INTO 
cust_fname,cust_lname 
FROM CUSTOMER_A1 
WHERE CUSTOMER_A1.cust_id=cust_id;

You probably meant this to mean "Find data from CUSTOMER_A1 where CUSTOMER_A1.cust_id = the value of the variable 'cust_id'". Unfortunately, that's not how it's interpreted. The database is reading this as "Find data from CUSTOMER_A1 where CUSTOMER_A1.cust_id = CUSTOMER_A1.cust_id" - in other words, it's comparing the CUST_ID field of each row to itself, finding that they're equal (except in the case of NULL values), and returns data from that row.

A good rule to remember when writing PL/SQL is "Never give a variable the same name as a column you'll be manipulating". With this in mind, you might consider rewriting your trigger as:

CREATE OR REPLACE TRIGGER invoice_after_reservation_made
  AFTER INSERT
  ON RESERVATION_A1
  FOR EACH ROW
DECLARE
  vInv_id INVOICE_A1.INV_ID%type;
  vRes_id INVOICE_A1.res_id%type;
  vRoom_id INVOICE_A1.room_id%type;
  vCust_fname INVOICE_A1.cust_fname%type;
  vCust_lname INVOICE_A1.cust_lname%type;
  vReservation_start_date INVOICE_A1.reservation_start_date%type;
  vCust_id RESERVATION_A1.cust_id%type;
BEGIN
  --read reservation_id
  vRes_id:= :new.res_id;

  --read room_id
  vRoom_id:= :new.room_id;

  --read reservation_start_date
  vReservation_start_date:= :new.reservation_start_date;

  --read customer_id
  vCust_id:= :new.cust_id;

  --create new invoice_id
  SELECT MAX(INVOICE_A1.inv_id)+1 INTO vInv_id FROM INVOICE_A1;

  -- import value from CUSTOMER_A1 table to variable cust_fname, cust_lname
  Select CUSTOMER_A1.cust_fname,CUSTOMER_A1.cust_lname
    INTO vCust_fname, vCust_lname 
    FROM CUSTOMER_A1 
    WHERE CUSTOMER_A1.cust_id=cust_id;

  -- Insert record into invoice table
  INSERT INTO INVOICE_A1
  VALUES (vInv_id, vRes_id, vRoom_id, vCust_fname, vCust_lname, null, 
          TO_DATE(TO_CHAR(reservation_start_date),'DD/MM/YYYY'), null);
END invoice_after_reservation_made;

Upvotes: 2

Related Questions