Re: Triggering on selects
Date: 15 Sep 1994 22:16:37 GMT
Message-ID: <35ah45$4u3_at_sbctri.sbc.com>
In article f0j_at_nemesis.muppet.bt.co.uk, crawford_at_strat-sys.bt.co.uk (Ben
Crawford) writes:
> >I want to fire a trigger when a SELECT operation is performed on a table,
> >potentially FOR EACH ROW. I'd be very interested in hearing if
> >anyone out there knows a way of doing this in Oracle V7!
> >
In Oracle 7.1 you can call database functions inside select statements. This might work for what you are attempting. You could create views for each base table and include your function somewhere in the select statement. Then have your users select against the view.
I don't have access to 7.1 to test this out, so I'm not sure if the function fires once or for each row. I'm also unaware of the restrictions (if any) placed on the function.
Here's an example I gleaned from "Mastering Oracle7" by S. M. Bobrowski;
select id
from orders
where customerid = sales.customermanager.findcustomerid('Brokaw', 'Thomas')
/
> >I want to do this in order to populate parts of the table as it is being
> >read - by making calls to other data stores. I need to do this 'on request'
> >because I am dealing with a large volume of volatile data, so keeping my
> >Oracle table fully in step with the rest of the world is not feasible.
Please remember I know little of your application needs, but I'm not sure that even if there were "select triggers" or the above "function approach" works, how realistic this algorithm is. Will you have your users wait, for a query which may return a very small number of tuples, while you load data into the base table? You would also be faced with timming issues. i.e. When I submit a query, the result set is consistent at a point in time. If a query runs for 5 minutes, and 100 rows are inserted and commited during this period of time, NONE are considered for the result set of my query.
> >
> >Any suggestions welcome; thanks for your time,
> >Ben.
Hope I've helped,
drt
-- David R. Thrash dthrash_at_sbctri.sbc.com Compuserve: 76217,1304 Thrash & Company 9102 Garland Road, Suite 216i Voice: 214.327.1972 Dallas, Texas 75218 Facsimile: 214.327.3510Received on Fri Sep 16 1994 - 00:16:37 CEST