Re: Anyone have a trigger solution?

> (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.

[Quoted] 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 [Quoted] 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 - 01:45:29 CET