Re: Triggering on selects

From: David R. Thrash <thrash_at_sbctri.sbc.com>
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.3510
Received on Fri Sep 16 1994 - 00:16:37 CEST

Original text of this message