Inquiry: Beyond replication

Hello,
 I need something that looks like a table but is actually a code block, or looks like a server but is actually a customizable code block, or some way to fire a trigger in response to a mere query without any update.
 More and more I'm finding places in my work where I need a local departmental copy of a view of an institution-wide table for heavy departmental research processing that shouldn't run against the institutional table because of sheer bandwidth and processing demands.  Self-refreshing replication ("CREATE SNAPSHOT...") helps a bit.  At the departmental and subdepartmental level, the clients to the tables are a large number of different custom apps, mostly built by third parties. When a user keys in a UniqueID, first thing a client app does is generally query the departmental table to load the form. If the data has previously been tracked within the department, the data will have been loaded from the institutional table.  There's the rub. Often data is requested in one department as a follow-up to a transaction in another department, and the desired data is now in the institutional data warehouse but hasn't been tracked by my department.
 So what I'm needing is to intercept certain types of queries that match some fairly simple patterns, and go into the following kind of code block:

1. Try the query against the department's table. If it returns any rows, return the resulting cursor to the client.
2. Else, if no data was found in the department's table, open a link to the institution's data warehouse and attempt to insert into the department's table the results of the same query issued against the data warehouse;
3. Re-issue the query against the department's table and return whatever comes back unconditionally.

So from the client's point of view, it looks like queries by unique ID against the departmental table respond with as much data as is in the data warehouse. The departmental table only contains rows for entities that are of interest to that department. End users are not asked to re-key any data that has been collected elsewhere in the institution. And none of the existing servers or clients should need to be rewritten as the potential complexity should be encapsulated.

I've been reading Oracle documentation and that of some other competitors and I haven't found a way of doing this sort of encapsulation, call- -need updating, etc.
  The closest I've come is to break into the code for a client, replacing the failure condition on the initial query with the insertion of a stub record in the departmental table, and running an insert trigger there to try to flesh out the record with data from the institutional data warehouse. This approach is unacceptable here as most of our client apps are not our property.

Has anybody else encountered this need and found a decent solution?

Matt Fields

Ann Arbor, MI Received on Mon Oct 12 1998 - 00:00:00 CDT