Re: Is there a trigger available for when a record is selected?

How about trying this B yourself and letting everybody know if it works? Should not be so difficult to do. At the first glance, Oracle should not allow you to create the view, failing with something like 'function does not garantee not to update the database'. You can probably work around this by hiding your inserts in a dynamic sql, and use a pragma to declare that you are not updating anything. It will likely compile and it will maybe crash with an ORA-600 down the road.

You certainly know about audit and that it can be used to record sql.

Ethel Aardvark wrote:

> I like a challenge, so here are two possibilities, the first of which
> will work but will need code changes, the second of which only
> mightwork but will not need code changes:
>
> A) Code Changes
> ~~~~~~~~~~~~~~~
> 1) Write a package of functions/procedures to encapsulate the table
> and put logging in there.
> 2) Force all code to use the package.
> [Note that this method is a good practice anyway as it allows the
> package be tuned to speed up performance by, for example, creating a
> simple cache to remember the last few requests made and returning
> results from package variables rather than the DB.]
>
> B) No Code Changes
> ~~~~~~~~~~~~~~~~~~
> This is a totally untested idea which I would try, but do not get your
> hopes up (also very subject to oracle versions - 8i and later only, I
> believe)...
>
> 1) Rename the table. Easier said than done, but possible.
> 2) Create a view with the name of the original table. This may need to
> handle inserts, updates & deletes so it very non-trivial too. Mind
> you, if it is static data you could be OK.
> [An alternative to the above is to just create a view and get your
> code to use that instead.]
> 3) Add a column to the view which is the result of a function of the
> tables's primary key (or use that in place of the primary key column).
> Easy, except for step 4.
> 4) The function needs to use an autonomous transaction to log the ID
> (and possibly other info such as date/time & user ID) in a separate
> table. I doubt this will work in the same transaction as the 'outer'
> SQL (you know what I mean) is running.
> 5) Say a prayer to whomever you believe is the God controlling these
> things. (Actually, I would try this before and after all stages of the
> above.)
>
> If you try method B and it works, please post a reply here saying so -
> I am keen to find out!
>
> Regards (and seasons greetings, etc.),
>
> ETA
>
> "Tim C" <NOTCornwell_at_NOTcs.NOTcornell.NOTedu> wrote in message news:<at830v$j96$1_at_news01.cit.cornell.edu>...
> > Hello all,
> >
> > I have a table of static data that I would like a simple method to record
> > read hits at the record level. Is there a method (trigger?) that I can build
> > that will record some record-level data whenever a record is part of a
> > select?
> >
> > Thanks In Advance,
> >
> > Tim
Received on Wed Dec 25 2002 - 03:19:56 CST