CODEWITHSUNDEEP
oracle-databaseplsqltriggerscursor
Mo Moosa
Mo Moosa

Reputation: 947

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

I'm still working on this database for a small retail store (a scenario, thankfully!) and am currently trying to fix this issue with a trigger.

The relevant entities are Customer, Payment and Order. Payment is a link entity between the other two, so one Customer could make many payments and one order could have many payments (unusual but still possible) and that's all fine.

The trigger:

CREATE OR REPLACE TRIGGER Check_Payment_Status  
BEFORE UPDATE OF Order_Status ON Customer_Order 

for each row
  DECLARE paymentStatus payment.payment_status%type;
  BEGIN

    select payment.payment_status into paymentStatus
    from payment
    where order_no = :new.order_no;

    IF (paymentStatus ='Failed' OR paymentStatus IS NULL ) then  
      RAISE_APPLICATION_ERROR(-20103, 'The full payment has not been made so the order cannot be     processed further until then.');
      update customer_order set order_status='Delayed' where order_no= :new.order_no;
    END IF;

    IF (paymentStatus ='Successful' ) then  
      update payment set payment_date=SYSDATE where order_no= :new.order_no;
    END IF;


  END; 
. 
run

It works okay at the moment. Basically, before a customer's order can be flagged as "Dispatched" the payment status must be "Successful". If it's null or "Failed" the trigger will be like "oh no you don't!" (but in more formal words) which is working as intended. However, if one applies the business rule "an order can have many payments" the trigger needs to check all relevant payments which is where I get this error, as the SELECT INTO statement is intending to return one row only.

I've read up on Cursors a bit but I think I'm going a bit too far here - would anyone suggest some solutions, please?

Upvotes: 1

Views: 10315

Answers (3)

Joe Stanton
Joe Stanton

Reputation: 41

I know this is an old question, but similar came up today in a client production database. Sometimes can be useful to order by the primary key, perhaps a sequence number, in descending order and use "rownum=1". Naturally you should find out why you have more than one record that matches the criteria and fix the bug in your application that allows it.

Upvotes: 4

furman87
furman87

Reputation: 968

On a general note, you have a lot of application-level logic tied up into a trigger, which will get you into a lot of trouble down the road because you're not going to know when and why values were updated in your related tables.

Dave Costa has a good suggestion for handling the payments, and I would create one or more related PL/SQL packages with clearly-written functions and procedures for handling the application-level logic that you're trying to perform in this trigger.

Triggers should be used very rarely -- typically when you're setting the primary key from a sequence or auditing access to a table.

Upvotes: 9

Dave Costa
Dave Costa

Reputation: 48111

Well, if an order can have multiple payments, how do you decide if it is paid in full? Presumably each payment has an amount, and the order has a total amount due, so you need to check that the full amount has been paid. Seems to me you can do this by getting the sum of all successful payments then comparing it to the total amount due. The basic query would be:

  SELECT SUM(payment_amount)
    INTO total_payment_amount
    FROM payment
    WHERE order_no = :new.order_no
      AND payment_status = 'Successful';

Upvotes: 9

Related Questions