Re: Anyone have a trigger solution?

> I was assuming that you were going to re-write the app from scratch.

I wish it was that simple. With 110 screens and 45 reports, it's a pretty big undertaking for 2 people, who also serve as the DBAs, network admins, and tech support staff. We have a plan to do a rewrite that would take approximately 6 man years, but we also get requests for "band-aiding" the application and day-to-day cleanup issues that consume a lot of time.

Can you imagine how much bad data is in a system with 176 tables and only 20 foreign keys and 22 primary keys??

> I'm not sure if 8.0.6 will handle nested tables, it will handle nested
> arrays, but then you have to pre-define the size of the array.

I think the nested table feature was added as part of pl/sql 8, so it's probably in most of the 8.0 releases, and we're planning to be on 8.1.6 (or newer) this year, as the servers have to all be running Windows 2000 by mid year, and those are the only releases that are certified.

> I would stay away from triggers for this app.

Ok, but what's the reasoning behind this statement?

> I'm also having difficulty in seeing why you need:
>
> 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;
>
> only to test the kount flag later,
> you appear to have the same tests in the update statment.
> can you not just remove the above code?

I don't see a reason for the count either. It's just extra. Either the update succeeded and the sql%rowcount equals the number of anticipated rows (the # of days to reserve, or the # of slots to reserve), or it failed.

> if you can combine this into the select for update then the problem will
> be solved.

Thanks again. Received on Wed Jan 17 2001 - 19:07:35 CET