Re: Triggering Statement in 8i

From: Rajagopal Venkataramany <rajagopalvr_at_excite.com>
Date: Wed, 23 Aug 2000 20:53:31 -0700 (PDT)
Message-Id: <10598.115437@fatcity.com>

Ian,

  There are 2 ways you can do this :

1. From the trigger, using the :OLD variables, you can find the values and sort of form the insert stmt (the catch here is that it will not be possible to differentiate between a column value that was provided a null value explicitly in the insert stmt vs the column not specified in the insert clause itself. In both cases the :OLD value for that column will be NULL)
2. You can locate the sqls used in a session from V$sqlarea. All the sqls can be retreived from this but it will be difficult to retreive just the insert sql stmt.

 My questions to u are :

1. Why do u need to do this ? I am quite puzzled...
2. When u say that u want to know the insert stmt, I guess u would want to know the exact stmt that was used viz.,

     insert into tableA values(a,b,c,d);

     insert into tableA(col1,col2) values (a,b);

     insert into tableA select * from tableB;
     
     insert into tableA(col1, col2) select col2, col2 from tableB;

Regards
Rajagopal Venkataramany

On Tue, 22 Aug 2000 13:12:29 -0800, ORACLE-L_at_fatcity.com wrote:

> Is there any way to determine which statement caused a trigger to fire?
In
> other words, if a trigger is fired when a row is inserted, can the insert
> statment be retrieved, and how?
>
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

Regards
Rajagopal Venkataramany

---

Say Bye to Slow Internet! Received on Wed Aug 23 2000 - 22:53:31 CDT