01403. 00000 'No data found'

Question

When I want to update or insert a row in which its zip is not in the zipcodes table, I got the 'no data found' error. I am confused because I already assigned a default value 67226 to validzip variable, how can they find no data? Thanks.

Answer 1

Your trigger needs to catch the NO_DATA_FOUND exception and substitute the default value in for the value in :NEW.ZIP:

create or replace trigger employees_bef_ins_upd_row
before insert or update of zip on employees
for each row
declare
  validzip employees.zip%type;
begin
  select zipcodes.zip 
      into validzip 
  from zipcodes 
  where :new.zip = zipcodes.zip;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :NEW.ZIP := 67226;
end;

That should satisfy your assignment, but in The Real World (tm) if you handed me this design for review I'd toss it back at you with a note on it saying "No business logic in triggers!". As explained here triggers should never be used to enforce business requirements - that's what application code is for.

Best of luck.

Answer 2

If a SELECT ... INTO ... statement returns no rows, it raises the NO_DATA_FOUND exception. Whether the target of the INTO has been previously initialized or not is irrelevant.

You need to add an exception handler to catch the NO_DATA_FOUND exception. Or, an alternative would be to change the query so it will always return a row; for example you could SELECT COUNT(*) which would return either 0 or 1.

Answer 3

I guess your problem is here :new.zip when you insert or update data , is the column zip have value 67226 ?

 select zipcodes.zip 
      into validzip 
  from zipcodes 
  where (:new.zip=zipcodes.zip);

and what do you mean by this :new.zip := validzip; ?

I guess the below might work( I am not sure because you have to specify you condition, its better to assign a default value unless you specify a condition )

update zipcodes set  :new.zip=67226 where zip is null