Re: trigger to solve these woes or what?

I do not understand why the second one overwrites the first. If the first sets a reservation ID where is was null the second one should not even find the row to update since it was trying to update the row where reservation_id was null. Or is it that the second one reads the first ones reservation_id and creates an update based upon that and NOT a null reservation id. Jim

"Eric Givler" <egivler_at_flash.net> wrote in message news:d1a86.4146$J%.468168_at_news.flash.net...
> 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
> outside vendor, and now we're attempting to clean up their mess. We'll
 call
> 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
> 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
> 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
> one simply WAITS on the other. When the 1st person commits, the second
 one
> then overwrites his work. OOPS! This isn't good.
>
> 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
> a while to cleanup the number of screens that are "miscoded".
>
> So....
> 1. Is this "availability dates" table really needed? (It's used
 throughout
> 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
> 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
> 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
> # of people, then after the statement is processed, check for an
 overlapping
> 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 Sun Jan 14 2001 - 00:49:56 CST