Re: Please help me - SQL - Urgent

From: Job <Job_at_pestilence.net>
Date: 2000/05/15
Message-ID: <391FE5F8.FBC30821@pestilence.net>#1/1

Winnie Liu wrote:
>
> Write a before insert trigger
>
> Winnie
>
> Paul wrote in message ...
> >Im trying to create a trigger for the table below (It has to be a trigger):
> >SQL> DESC BOOKING
> > Name Null? Type
> > ------------------------------- -------- ----
> > PATIENT_NO VARCHAR2(6)
> > ROOM_NO NUMBER(3)
> > RESV_DATE DATE
> > PTO_DATE DATE
> >
> >What i want to do is ensure that when I try to insert a row that has dates
> >that fall between or on the RESV_DATE and PTO_DATE of an already existing
> >row (these are a booking start and end date) the insertion is rejected.
> >Ive tried my best over and over and cannot seem to achieve it. I am at the
> >end of my teather :( Please help an sql newbie out.
> >Thanks
> >Paul

begin
select * from booking where room_no = new_room_no

	and trunc(new_resv_date) >= trunc(resv_date) 
	and trunc(new_resv_date) <= trunc(pto_date);

rollback; /* should only do this if one row exists */ exception when no_data_found then

	continue;  /* okay to insert */
	when too_many_rows then
	rollback; /*exists already*/
	

end;

You'll need a couple of change but this should work.

end if; Received on Mon May 15 2000 - 00:00:00 CDT