Re: trigger to solve these woes or what?

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 select statement). The update statement that followed just goes into a wait state until the first session commits. The first session would have had to 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 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".

Does that make more sense?

"Jim Kennedy" <kennedy-family_at_home.com> wrote in message news:o_b86.262434$U46.8392673_at_news1.sttls1.wa.home.com...
> 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 - 16:27:24 CST