Re: Anyone have a trigger solution?

Hi,
[Quoted] can you not use a nested table inside the record for the item you want to allocate.
each time there is an allocation, enter it into the nested table, then order by insertion time/date, so that the first item in always overrides the later additions.
this would also have the advantage of showing how popular an item is, and if the person at the top cancels, you can drop down 1 item.

once the transaction is committed by both users, first come first serve. let the other user know it is not available, a simple check of the current details against the top item will confirm who has the item.

Eric Givler <egivler_at_flash.net> wrote:

> > (a) At least one fix is easy: if you want to check overlaps, do not use
 the
> > count(*) method, but the package function I posted before (i.e. use a
 cursor
> > that selects overlaps and halt on the first sucessfull fetch).
>
> Yes, thanks much!
>
> > (b) Of course this "available dates" table can be replaced with a proper
> > index system on your main table that does exactly the same.
>
> Could this be done in triggers? Since there's several screens (5+) where
> reservations can make it into the system and the code is a MESS, I'd like to
> clean up this mess "behind the scenes" (if possible). I'm worried about the
> dreaded mutating table error. In my case, do I just hold the
> reservation_eid in a row level trigger than validate the table in an after
> insert/update statement trigger?
>
> > (c) Lost updates can be avoided with a SELECT FOR UPDATE. Unfortunately,
> > this will wait until able to grap the stuff.
>
> I was thinking of a SELECT FOR UPDATE NOWAIT;
> If I can't get the rows, it immediately fails. I'd
> raise_application_error(-20001,'That site/facility is reserved by another
> user');
>
> > You could alternatively use a
> > "soft lock", i.e. an indicator field (typically holds the user ID) that
 you
> > test and eventually update. And not wait of course, but signal the user
 that
> > someone else has grabbed the obejct of desire. This is fairly
> > straightforward to implement: if empty, you set the indicator (with a
 commit
> > of course) before you do anything else and you empty it after you commit
 or
> > rollback your actual work. Of course, if you DO set the indicator, you
 also
> > SELECT FOR UPDATE, so that applications that are unaware of your softlock
> > can't mess around with your data.
>
> This is the same method thery use now and it doesn't work (other than the
> select for update, but I don't see how that helps unless you add the
> NOWAIT). They place the "terminal" that is making the reservation in the
> reservation field. This is called "holding" the site. When the reservation
> is completely entered, the # is changed from the terminal to the
> reservation#. I think one of the problems is that they don't perform an
> immediate commit though. So, other sessions can't read it, and then they go
> into a wait when they try to perform the update. Can you elaborate?
>
> THANKS.
Received on Tue Jan 16 2001 - 06:56:52 CET