Anyone have a trigger solution?

[Quoted] (originally posted in comp.databases.oracle.server)

We've an application at work that has a number of problems with duplicate/overlapped reservations for the same site. This was written by an [Quoted] outside vendor, and now we're attempting to clean up their mess. We'll call [Quoted] this a "double-booking issue".

The design of the application is to allow the public to reserve facilities at our state parks (in PA). Each facility,ie. a campsite, cabin, private dock, etc, has a date range of availability. We call that a season. A season has an open and close date. So, each site has a season. Once the seasons are established for all sites, the dba runs a utility called populatedates. This places an entry into a table for EVERY SITE, for EVERY [Quoted] DAY that it is available. The table is very large, and we remove old data out of it when it gets over 2 months old.

What makes the table even larger is that some sites allow multiple reservations per day: ie. WhiteWater Boating. This has 8 timeslots a day, and up to 60 people per slot. So, if the season is from may through september, you can 7*60 * # days in season rows in this table! WOW!!

Anyway, each time a reservation is entered in the system, an entry is created into another table called: reservations. The reservation has an arrival date and a departure date and # of people in the party. The corresponding rows in the "availability dates" table have a flag that now contains the reservation# to show that they are "booked".

Unfortunately, the brilliant programmers used this method to check whether a [Quoted] site was open:

select count(*) into Kount
from available_dates
where site_id = my_site
and reservable_date between arrival_date and departure_date and reservation_id is null;

if kount = 0 then

   update available_dates
   set reservation_id = my_res_id
    where site_id = my_site
    and reservable_date between arrival_date and departure_date     and reservation_id is null;
end if;

• there's other code that differentiates for the # of people in the party based on the site type. The problem occurs in that two users can attempt to grab the same site. The [Quoted] one simply WAITS on the other. When the 1st person commits, the second one [Quoted] then overwrites his work. OOPS! This isn't good.

The second one overwrites the first because it sees the reservation_id has not been populated by the first one yet (it is still null on the read - the [Quoted] select statement). The update statement that followed just goes into a wait [Quoted] state until the first session commits. The first session would have had to [Quoted] COMMIT the data prior the second one doing the update, then the second session would see the row was changed,and the update would fail
(reservation_id would be not null). You can verify this real quick in
SQL*Plus as the second session will simply wait for the first session to COMMIT the data. At that point, it will perform it's update and overwrite the original.

I probably didn't spell this out, but the updates to this table are done in [Quoted] forms, but the table that is updated is not tied to a forms block - it's just an update statement. So, we don't get the standard ORacle error "Record changed or deleted by another user" or the "Attempting to reserve record error".

I was tempted to write some select statements with a for update on the end and if I couldn't get the rows that I wanted, then I would think someone else is trying to do something with the rows that I want. BUT, there are SEVERAL avenues in the system that allow this activity. It's going to take [Quoted] a while to cleanup the number of screens that are "miscoded".

So....
1. Is this "availability dates" table really needed? (It's used throughout [Quoted] the entire application now which consists of over 100 screens and 45+ reports, so it will be difficult to remove in the NEAR FUTURE).

2. Should I attempt to encapsulate all calls that reference this table into [Quoted] a package and make that the interface to this "cumbersome" table?

3. Should I also use the "select for update" to initially make sure the site [Quoted] is available for all days in question?

4. Should I make use of some form of triggers? (not sure what without preventing a mutating table) that I would capture the site#, start, end and [Quoted] # of people, then after the statement is processed, check for an overlapping [Quoted] reservation in the system, or check that the # of people in the party matches the # of rows "reserved/flagged" in the database?

Any suggestions are appreciated!! This is one of the bigger problems in this system, and I'd like to get it fixed ASAP. Right now, my approach is #2 and #3.

I was somehow thinking that #1 combined with #4 would also work, possibly still using a package though. Received on Mon Jan 15 2001 - 20:06:17 CET