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:
- Code Changes
- Write a package of functions/procedures to encapsulate the table
and put logging in there.
- 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.]
- 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)...
- Rename the table. Easier said than done, but possible.
- 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.]
- 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.
- 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.
- 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 Fri Dec 20 2002 - 05:18:10 CST