We help IT Professionals succeed at work.

Oracle Trigger - ORA-01403 No Data Found

xbox360dp
xbox360dp asked
on
Gurus,

I need a little help with a trigger I wrote.

create or replace
TRIGGER update_element_layout BEFORE
  INSERT OR
  Update On Pro For Each Row
 
  Declare
Element_Layout_Name Varchar2(2000);
 
  Begin
  Select Distinct Rafbezc
    Into Element_Layout_Name
    From Raf where rafkeyi = :new.prorafkeyi;
      If
    :New.Prorafkeyi Is Not Null And :New.Prorafkeyi <> 0
Then
      :New.Provf7c     := Element_Layout_Name;
   END IF;
  END;

The problem is with the following statement:

Select Distinct Rafbezc
    Into Element_Layout_Name
    From Raf where rafkeyi = :new.prorafkeyi;


This statement isn't always true and I get a ORA-01403 when its not.

Is there a better way to write the trigger so that I can avoid this error?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
What is the 'default' value you want if the select returns no rows?

the quick answer is nested pl/sql blocks with a custom exception handler.



create or replace
TRIGGER update_element_layout BEFORE
  INSERT OR
  Update On Pro For Each Row 
  
  Declare 
Element_Layout_Name Varchar2(2000);
  
  Begin

   begin
  Select Distinct Rafbezc
    Into Element_Layout_Name
    From Raf where rafkeyi = :new.prorafkeyi;
      exception when others then
            Element_Layout_Name := 'Some default value';
    end;

      If 
    :New.Prorafkeyi Is Not Null And :New.Prorafkeyi <> 0 
Then
      :New.Provf7c     := Element_Layout_Name;
   END IF;
  END;

Open in new window

Author

Commented:
Perfect!!

Thanks!