RE: Triggering Statement in 8i

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Thu, 24 Aug 2000 15:06:17 -0700
Message-Id: <10599.115517@fatcity.com>

I'm looking at "after error" trigger. I'm trying to trap the statement which caused the error. It appears, when such a trigger fires the statement which triggered the firing is no loger associated with the session. What is associated is the sql in the body of the trigger itself.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu
-----Original Message-----
From: Rajagopal Venkataramany [mailto:rajagopalvr_at_excite.com] Sent: Wednesday, August 23, 2000 10:05 PM To: Multiple recipients of list ORACLE-L Subject: Re: Triggering Statement in 8i

Ian,

There are 2 ways you can do this :

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)
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 :

Why do u need to do this ? I am quite puzzled...
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!
http://www.home.com/xinbox/signup.html

-- 
Author: Rajagopal Venkataramany
  INET: rajagopalvr_at_excite.com

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

Received on Thu Aug 24 2000 - 17:06:17 CDT