CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
kate8895
kate8895

Reputation: 27

Errors when trying to create trigger (invalid identifer & sql ignored)

I am working within a hotel database. I am attempting to create a trigger that will change the availability status of a room before any new reservations are made based on if the current date falls between the check_in and check_out values of a room.

I have 2 errors from my compiler log:

SQL Statement Ignored on 'for each row'

and

ora00904: "status_in":invalid identifier

I would appreciate any assistance.

Tables:

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 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)
);

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_id = :old.room_id;

    if sysdate between :old.check_in and :old.check_out then
      update room
       set status_in = 'F'
       where room_id = :old.room_id;
    else
       update room
         set status_in = 'T'
         where room_id = :old.room_id;
    end if;

exception
  when error1 then
    raise_application_error(-20100,'Insert Cancelled');
end;

Upvotes: 0

Views: 88

Answers (1)

Glenn
Glenn

Reputation: 9150

The update statement tries to update column status_in of room:

update room
  set status_in = 'F'

but room does not have that column. It has a status column.

I'm also not sure what your intentions were regarding retrieving the old room status. Were you planning on using that to check something? If not, was your trigger just going to do something like this:

update room
  set status = (sysdate between :old.check_in and :old.check_out)
  where room_id = :old.room_id
;

If so, then you might also question whether you really want a status column on the room table at all. status is actually a derived column. Setting the status rather than computing it will leave you with the danger of an inconsistency. Instead, you could do something along these lines:

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

The querying:

SELECT r.room_id
      ,r.room_type
      ,SUM(CASE WHEN sysdate between :old.check_in and :old.check_out THEN 1 ELSE 0 END) AS status
  FROM room r
  JOIN reservation v ON (v.room_id = r.room_id)
  GROUP BY r.room_id, r.room_type
  ;

Upvotes: 1

Related Questions