Re: Triggers
Date: Wed, 9 Sep 2009 06:19:40 -0700 (PDT)
Message-ID: <56df1adc-946e-4e75-820e-38a9d2cb75cf_at_y36g2000yqh.googlegroups.com>
On Sep 8, 12:05 pm, The Magnet <a..._at_unsu.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.
>
> If there are others, I'm all ears.
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 -- Received on Wed Sep 09 2009 - 08:19:40 CDT