Re: Triggers
From: joel garry <joel-garry_at_home.com>
Date: Tue, 8 Sep 2009 14:05:24 -0700 (PDT)
Message-ID: <da0fde07-5eb1-4cbc-b886-00a114ca71e2_at_d15g2000prc.googlegroups.com>
On Sep 8, 1:10 pm, ddf <orat..._at_msn.com> wrote:
> On Sep 8, 11:28 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
> > On Sep 8, 11:20 am, ddf <orat..._at_msn.com> wrote:
>
> > > On Sep 8, 11:05 am, 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.
>
> > > 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
>
> > 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?- Hide quoted text -
>
> > - Show quoted text -
>
> Unless you set it through a call to
> dbms_application_info.set_client_info it should appear in the PROGRAM
> column.
>
> David Fitzjarrell
Date: Tue, 8 Sep 2009 14:05:24 -0700 (PDT)
Message-ID: <da0fde07-5eb1-4cbc-b886-00a114ca71e2_at_d15g2000prc.googlegroups.com>
On Sep 8, 1:10 pm, ddf <orat..._at_msn.com> wrote:
> On Sep 8, 11:28 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
> > On Sep 8, 11:20 am, ddf <orat..._at_msn.com> wrote:
>
> > > On Sep 8, 11:05 am, 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.
>
> > > 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
>
> > 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?- Hide quoted text -
>
> > - Show quoted text -
>
> Unless you set it through a call to
> dbms_application_info.set_client_info it should appear in the PROGRAM
> column.
>
> David Fitzjarrell
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
-- _at_home.com is bogus. http://www.bu.edu/today/campus-life/2009/08/17/rotc-computer-files-found-public-domainReceived on Tue Sep 08 2009 - 16:05:24 CDT