Re: Anyone have a trigger solution?

hi,
sounds like my ideal job,
You think you have it bad!
[Quoted] I'm paid as a Q.A manager and I have been plodding along for 6 man years, (since 95), with 1 programmer dba,network admin,viri killer etc. (me!).

[Quoted] I would suspect that the forms are basically good, try to turn the situation round, currently you are storing a huge amout of un-needed data.

[Quoted] you have a season_start & a season_end , I'm assuming that there are no dead days,
I.E the season is continuous.
therefore store only the days that have been allocated.

[Quoted] I think you would be better off with nested tables, that way you can allocate your resources, and store the dates allocated in the nested table. it would only be a simple matter to scan for allocated days in the relevent resource, cleanup would also be a doddle, as would the update problem you are having.

[Quoted] try modifying one of your tables to add a nested table, then in the background write some code that transfers the data into the nested table, compares the results against the reservations, and if they are not the same , stick an entry into a table (e.g dba_evaluate) that you can browse, to pickup any errors.
so that you would be double storing data, after a few weeks, you will have an evaluation period for free, where you can see the results.

[Quoted] then gradually patch your application, so that you take the data from the nested tables, eventually you will be in a position to, get rid of some of your tables ( reservations, bye bye forign keys etc)

[Quoted] you also don't say if you are using the advanced replication in the 8.0.6 ee version.  

p.s
[Quoted] whats a forign key?
steve

Eric Givler <egivler_at_flash.net> wrote:

> > 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 Thu Jan 18 2001 - 02:44:18 CET