pl/sql trigger error : exact fetch returns more than requested number of rows

Question

I am having a tough time understanding what is wrong with my pl/sql trigger. The error is : Error report - SQL Error: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYSTEM.TRG_LATE_RETURN", line 6 ORA-04088: error during execution of trigger 'SYSTEM.TRG_LATE_RETURN' 01422. 00000 - "exact fetch returns more than requested number of rows" *Cause: The number specified in exact fetch is less than the rows returned. *Action: Rewrite the query or change number of rows requested

My trigger is as follows:

create or replace trigger trg_late_return
before update of DETAIL_RETURNDATE, DETAIL_DUEDATE on TY_DETAILRENTAL

declare 
  temp_date DATE:= SYSDATE;
  temp_due_date DATE:= SYSDATE;

BEGIN
  select DETAIL_RETURNDATE
  into temp_date
  from TY_DETAILRENTAL;

  select DETAIL_DUEDATE
  into temp_due_date
  from TY_DETAILRENTAL;

  IF temp_date is null
  THEN
    update TY_DETAILRENTAL
    set DETAIL_DAYSLATE=null;
  END IF;

  if temp_date <> null
  THEN
    if temp_date = trunc(temp_due_date) + 1
    then
      update TY_DETAILRENTAL
      set DETAIL_DAYSLATE=0;
    end if;

    if temp_date > trunc(temp_due_date) + 1
    then
      update TY_DETAILRENTAL
      set DETAIL_DAYSLATE = DETAIL_RETURNDATE - DETAIL_DUEDATE;
    end if;  
  end if;

END;
/

New to SQL and PL/SQL so I would appreciate any help.

I doubt TY_DETAILRENTAL has exactly one row, so your select . . . into statements are not going to work. — Gordon Linoff

Alex Poole Alex Poole · Accepted Answer · 2017-03-05T15:50:12

None of your queries or updates have filters (where clauses) so you are working on the entire table - that can't be what you intended. You are trying to get all of the value of those columns into scalar variables, which can only hold a single value.

The trigger is also against the same table you're querying and updating, which suggests you actually meant this to be (or need this to be) a row level trigger, not a statement level trigger.

That means you need to add for each row to the definition, and instead of requerying and updating the table, you can operate on the new pseudorecord that is available for row level triggers.

You also can't use <> to compare with null; you're already using is null, and the opposite of that is is not null. Although you could also just use else here.

So this might be what you wanted:

create or replace trigger trg_late_return
before update of detail_returndate, detail_duedate
on ty_detailrental
for each row
begin
  if :new.detail_returndate is null
  then
    :new.detail_dayslate := null;
  else
    if :new.detail_returndate = trunc(:new.detail_duedate) + 1
    then
      :new.detail_dayslate := 0;
    elsif :new.detail_returndate > trunc(:new.detail_duedate) + 1
    then
      :new.detail_dayslate := :new.detail_returndate - :new.detail_duedate;
    end if;  
  end if;
end;
/

You can refer to the updated columns value with :new when comparing them too, so you don't need the local variable copies of those values. (You can look at the pre-update values with :old too, but that doesn't seem to be needed here).

Changes you make to the new pseudorecord are used when the actual matching row in the database is finally updated.

Read more about the new/old pseudorecords, and about triggers in general.

pl/sql trigger error : exact fetch returns more than requested number of rows

1 Answers