double book should not be allowed - trigger problem

[mrasad]

Hi

I wanted to create a trigger to fire on insert or update, when a room had already been booked. The reserved_For,reserved_Until are both Date fields and have the following format (to_date('21-11-2003 09:00','DD-MM-YYYY HH24:MI'),to_date('21-11-2003 11:00','DD-MM-YYYY HH24:MI').

I've create the following trigger and although the trigger is created, it does not work as when I try to enter the same infomation with a differnt pk it allows me to;

CREATE OR REPLACE TRIGGER DBRoom
BEFORE INSERT OR UPDATE OF reserved_For,reserved_Until
ON room_rev
FOR EACH ROW
BEGIN
IF new.reserved_For LIKE ld.reserved_For)
AND new.reserved_Until LIKE ld.reserved_Until)
THEN
RAISE_APPLICATION_ERROR(-20000, 'Room is Booked, try again');
END IF;
END DBroom;

------------------------------------------
Any suggestions?

 

[BJCooperIT]

If this is an INSERT, how was the room previously booked? On an insert there is no :OLD value to compare against.

LIKE is not what you need for this comparison -it requires a '%' for matching patterns. For example, looking for 'SMITH%' to match 'SMITH' or 'SMITHSONIAN'.

If I read this right, the row has a begin and end date which means you need date range logic.
If the ld range were (abbreviated for display purposes):

       05-JAN, 06-JAN, 07-JAN

the :new ranges to fail would include:

04-JAN 05-JAN
       05-JAN  06-JAN
               06-JAN
               06-JAN  07-JAN
                       07-JAN  08-JAN

You want to fail if any date in the :new range falls within ld range.

The following logic is for updates based on your code:

IF (:new.reserved_For BETWEEN
    :old.reserved_For and :old.reserved_Until)
OR (:new.reserved_Until BETWEEN
    :old.reserved_For and :old.reserved_Until)
OR (:old.reserved_For BETWEEN
    :new.reserved_For and :new.reserved_Until)
OR (:old.reserved_Until BETWEEN
    :new.reserved_For and :new.reserved_Until)
THEN
RAISE_APPLICATION_ERROR(-20000, 'Room is Booked, try again');
END IF;

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[SantaMufasa]

Mr. Asad,

Is it correct that each (attempted) room reservation is a new/separate row? As an example, a reservation for 'J. Smith' in room '1107' during '05-JAN-04 10:00:00' thru '06-JAN-04 12:00:00' is one row, and a reservation for 'M. Jones' in room '1107' during '05-JAN-04 15:00:00' thru '06-JAN-04 09:00:00' is another row; and whichever row is "second", because of the time overlap, should reject? If so, then we'd need to modify BJ's code, above.

Please confirm your situation, and, again, we can write code for you.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:51 (01Jan04) GMT, 12:51 (01Jan04) Mountain Time)

 

[BJCooperIT]

Dave makes a good point. By no means was my 1st post intended to be a complete solution to your problem. I simply wanted to address the date logic, while questioning how new rows are to be validated against previous bookings.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[mrasad]

Hi

There are 10 rooms that can be booked by members. Their should never be that case that a room i.e 001 which has been booked between 14:00 & 15:00 to be booked again. If a member wanted however to book from 15:01 to 17:30, then this should be allowed. A room can be booked for a minimal of one hour. The room_rev table is below;

create table Room_Rev
(RoomRevID varchar2(6) PRIMARY KEY,
purpose varchar2(20) not null,
reserved_On date not null,
reserved_For date not null,
reserved_Until date not null,
Booking_Fee number(5,2) not null,
RoomNO REFERENCES room,
MemberID REFERENCES Member);

Some example data;
insert into Room_Rev
values
('RR001',
'lecture',
to_date('19-11-2003 15:00:00','DD-MM-YYYY HH24:MI:SS'),
to_date('21-11-2003 09:00','DD-MM-YYYY HH24:MI'),
to_date('21-11-2003 11:00','DD-MM-YYYY HH24:MI'),
100.00,
'001',
'1');

insert into Room_Rev
values
('RR002',
'lecture',
to_date('19-11-2003 15:00:00','DD-MM-YYYY HH24:MI:SS'),
to_date('21-11-2003 09:00','DD-MM-YYYY HH24:MI'),
to_date('21-11-2003 11:00','DD-MM-YYYY HH24:MI'),
'100.00',
'002',
'3');

insert into Room_Rev
values
('RR003',
'conference',
to_date('21-11-2003 15:00:00','DD-MM-YYYY HH24:MI:SS'),
to_date('24-11-2003 10:00','DD-MM-YYYY HH24:MI'),
to_date('24-11-2003 12:00','DD-MM-YYYY HH24:MI'),
'250.00',
'006',
'4');

------------

do any of the above help matters?

 

[SantaMufasa]

Mr. Asad,

To be direct, do you have a specific question about YOUR code/logic that BJ and I have not answered for you, or would you like BJ, or me, or someone else to write your code for you? The leadership of Tek-Tips asks us to focus on providing professional tips and helpful hints rather our becoming a "Free Code-Writing Service".

Although we (Tek-Tipsters) are generally eager to provide help, I believe the leadership is correct in their guidance that we answer specific technical questions rather than write code to solve applications.

Please restate any specific ORACLE question or general Oracle programming inquiry for us to help you with.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:31 (01Jan04) GMT, 13:31 (01Jan04) Mountain Time)

 

[mrasad]

Hi,

point taken. I'm very new to pl/sql and I am still in the process of learning. I need to create similar triggers to the one above, and if I can get one functioning correctly I can work on other triggers. I have tried, but have had no luck so far.

I do value the work you guys do on this forum, and I am hoping in the future to help others like myself on forums such as this (when I become as good as you guys)

Many thanks for all your help
Asad