Trigger error: ORA-01403 no data found on insert

Question

My database is that of a hotel reservation system. My trigger will change the availability status of a room before any new reservations are made based on whether the current date falls between the check_in and check_out values of a room.

I getting the error no data found when attempting to fire the trigger with an insert statement. I would appreciate any assistance with the problem.

Error:

SQL Error: ORA-01403: no data found ORA-06512: at "USER.ROOM_STATUS", line 5 ORA-04088 error during execution of trigger 'USER.ROOM_STATUS' - no data found *Cause: No data was found from the objects

Trigger:

create or replace trigger room_status
  before insert on reservation
  for each row
declare
  status_in room.status%type;
  error1 exception;
begin
  select status into status_in
  from room
  where room.room_id = :old.room_id;
  if sysdate between :old.check_in and :old.check_out then
    update room
    set status = 'F'
    where room_id = :old.room_id;
  else
    update room
    set status = 'T'
    where room_id = :old.room_id;
  end if;
exception
  when error1 then
    raise_application_error(-20100,'Insert Cancelled');
end;

Insert Statement:

insert into reservation
values(reservation_sequence.nextval,to_date('05-03-2017','mm/dd/yyyy'), 
to_date('05-07-2017','mm/dd/yyyy'), 116, 170);

Tables:

create table roomType
(room_type varchar2(20) constraint roomType_pk primary key,
room_rate number(4));

create table room
(room_id number(3) constraint room_pk primary key,
room_type varchar2(15) constraint room_fk references roomType,
status char(1));

create table guest
(guest_id varchar2(5) constraint guest_pk primary key,
first_name varchar2(20),
last_name varchar2(20),
email varchar2(30),
phone varchar2(10));

create table reservation
(reservation_id number(6) constraint reservation_pk primary key,
check_in date,
check_out date, 
room_id number(3),
guest_id varchar2(5),
foreign key (room_id) references room(room_id),
foreign key (guest_id) references guest(guest_id));

APC APC · Accepted Answer · 2017-05-07T07:10:36

Oracle throws the NO_DATA_FOUND error when a SELECT statement finds no data. So, looking at your trigger, the obvious explanation might be there is no data in your ROOM table.

However, there is a subtle bug in your trigger code:

select status into status_in
from room
where room.room_id = :old.room_id;

The :old namespace references the value of the column before the DML is applied. But your DML statement is an insert so the values of :old.room_id is null because there is no prior record. Anything = null is always false so the select fails to find anything.

What you need to do is reference the :new value.

select status into status_in
from room
where room.room_id = :new.room_id;

:new applies to INSERT and UPDATE statements, :old applies to UPDATE and DELETE statements.

Incidentally you have defined an exception error1 but you don't raise it anywhere so that exception handler will never execute.

Trigger error: ORA-01403 no data found on insert

1 Answers