Re: Oracle Trigger Error, pls help...

Unfortunately, you can't check the results of your select statement inline with an IF
statement. Try declaring a variable to receive guest_no and comparing it after the select:

<<fragment>>
declare
been_booked exception;

guest_name           varchar(20);
last_date         date;
guestno          number;

begin

        select guest_no
        into  guestno0
        from booking
        where room_no = :new.room_no
           and hotel_no = :new.hotel_no;

        if guestno is not null then
             raise been_booked;
        end if;

<<fragment>>

Ken Shirey
OCP DBA/AppDev
Hencie Consulting Services

kickerchua_at_audiophile.com wrote:

> hi all, i have been trying to build this trigger on my Personal Oracle 8 but
> it gives me a compilation error, anybody knows where did i do it wrong?
>
> all i wanted to do is to halt any booking on those rooms that has been already
> booked. and
> give the guest_name (the person who book the room) and the date when a booking
> can be made.
>
> thanks for your time. pls reply to me via email to mailto:choonkeng_at_yahoo.com
> , thanks in advance.
>
> ================================================================================
>
> create trigger booked before insert on booking
> for each row
>
> declare
> been_booked exception;
> guest_name varchar(20);
> last_date date;
>
> begin
> if ( select guest_no
> from booking
> where room_no = :new.room_no and
> hotel_no = :new.hotel_no ) is not null
> then raise been_booked;
> end if;
>
> exception
> when been_booked then
> (select name into guest_name
> from guest
> where guest_no =
> ( select guest_no
> from booking
> where room_no = :new.room_no and
> hotel_no = :new.hotel_no );
> select date_to+1 into last_date
> from booking
> where room_no = :new.room_no and
> hotel_no = :new.hotel_no;
>
> raise_application_error (-00027, 'You cannot book this room
> because it
> had been booked by ' || guest_name'. You can book this room by
> ' || to_char (last_date)'.');)
> end;
>
> ===============================================================================
>
> PLS-00103: Encountered the symbol "SELECT" when expecting one of the
> following:
>
> ( - + mod not null others <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> avg
> count current exists max min prior sql stddev sum variance
> cast <a string literal with character set specification>
> <a number> <a single-quoted SQL string>
> PLS-00103: Encountered the symbol "NOT" when expecting one of the following:
>
> ) intersect minus union
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
Received on Sun Aug 16 1998 - 11:41:02 CDT