Hi,

Probably a nutty question, but, is it possible to find out what
program contained the DML which fired the trigger?

We have a particular situation, and next to mining the logs, we're not
sure how to find who or what is updating a certain row. So, we should
a trigger on the table testing for this particular user ID and
capturing information is the best way to go.

If there are others, I'm all ears.

On Sep 8, 11:05*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
You could set event 10046 at the instance level, but that would be
overkill, I think. An after update trigger would work, provided you
populate an auditing table with the desired information. You'll need
to regularly monitor the table for size, unless this is a 'one-shot'
event where once you find the 'cuplrit' you disable the trigger.

Of course others may have differing opinions.


David Fitzjarrell

On Sep 8, 11:20*am, ddf <orat... (AT) msn (DOT) com> wrote:
Thanks David. The only thing is that we're looking for the program
name. Say it is an external application with a direct DML statement.
Would that appear in PROGRAM or CLIENT INFO of V$SESSION?

On Sep 8, 11:28*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
Unless you set it through a call to
dbms_application_info.set_client_info it should appear in the PROGRAM
column.


David Fitzjarrell

On Sep 8, 1:10*pm, ddf <orat... (AT) msn (DOT) com> wrote:
I'd add, some programs do odd things, so look at the various fields
like module, program, terminal, client_info and see what's there. If
it is all blank and you are running some program visible at the OS
level, you may need to look at v$process and spit out the process to
an os utility. (On the ERP I work on, it's easy to find the DML, but
very difficult to find the generator code that created it). It
depends.

jg
--
@home.com is bogus.
http://www.bu.edu/today/campus-life/...-public-domain

The Magnet (art (AT) unsu (DOT) com) wrote:

: Hi,

: Probably a nutty question, but, is it possible to find out what
: program contained the DML which fired the trigger?

: We have a particular situation, and next to mining the logs, we're not
: sure how to find who or what is updating a certain row. So, we should
: a trigger on the table testing for this particular user ID and
: capturing information is the best way to go.

As an aside, it may be useful to use PRAGMA AUTONOMOUS_TRANSACTION; so
your code will save the logged details even if the caller rolls back.
(And don't forget to COMMIT your AUTONOMOUS_TRANSACTION if you do do
this.)

You can use it directly in the trigger, or in a function, so I understand.

On Sep 8, 12:05*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
If you are talking about activity that took place in the past then the
answer is no. I do not believe you will be able to find information
about the session that fired the SQL via log mining. You will be able
to find the SQL itself but I do not think the detailed session
information will be there.

In your case since you want to try to capture the v$session.program
value I believe you need to use the table level trigger option. Be
aware that depending on the version of the Oracle client in use that
the v$session.program column is not always populated. Also depending
on the type of client you may end up with something like JDBC thin
client which does not do much to help you tie down exactlty what
program is running since you probably want to identify the specific
piece of programming code that submits the SQL in question.

If the application in question is written to use dbms_application_info
then the v$session.module column may also be populated but it will be
null otherwise. As previously mentioned it would not hurt to check on
this column before you design your trigger to see what information is
available from the sessions you believe to be of interest.

HTH -- Mark D Powell --