Re: What Attributes are available to a Trigger

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Wed, 26 Jun 2002 11:28:43 -0800
Message-ID: <F001.00489300.20020626112843@fatcity.com>

johanna.doran_at_sungard.com wrote:
>
> Hi,
>
> I know for example, I can access :new values and :old values. I also thought that I could access the TYPE of ddl that caused the trigger to fire and I am wondering if I have access to the sql that caused the trigger to fire.
>
> I am looking in the application development PL/SQL manual but I am not seeing a list anywhere.
>
> Thanks,
>
> Hannah

OK, Hannah, if you are running 9i there is a function named ora_sql_txt which returns (as a table of strings) the text of the SQL statement which fired a trigger when called within that trigger. It is documented with triggers on DDL but works with 'standard' triggers too. If you are on 8.1.7 run the following as SYS, it gives you the same functionality. Prior to 8.1.7 you will not have the 'bulk collect' and the ora_name_list_t may not be defined, so there should be a bit of tinkering but basically it's the same. Adapting the query to 7.3 shouldn't be too difficult either, I can't guarantee it's strictly identical but it's the first Oracle version I wrote this kind of thing on, so it can be done.

A word of caution when called in a DDL trigger : you can get the text for some statements (ALTER TABLE, TRUNCATE) but not for others (CREATE TABLE, CREATE INDEX - I think that 9i returns it in this case, however). Nevertheless, RTFMing will point you to the functions which return you command type and object name, then it's just a nice stroll in the data dictionary to piece the various bits together. Beware also of some DDL commands (ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY ...) which fire the trigger twice (once for the constraint,once for the associated index) - but return the same statement both times.  

create or replace function orio$sql_txt (sql_text out ora_name_list_t) return binary_integer
is
begin
  select a.name
  bulk collect into sql_text

  from x$kglna a,
       x$kglcursor b,
       x$kxscc c
  where c.kxsccsta != 0
    and c.kxsccflg like '%/TRG'
    and c.kxsccfl2 not like '/EOF/%'
    and c.inst_id = sys_context('USERENV', 'INSTANCE')
    and c.kxsccphd = b.kglhdadr
    and c.inst_id = b.inst_id
    and b.kglhdpar = a.kglhdadr

    and b.kglnahsh = a.kglnahsh
    and b.inst_id = a.inst_id
  order by a.piece;
  return(sql_text.count);
exception
  when no_data_found then
    return 0;
end;
/

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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
also send the HELP command for other information (like subscribing).

Received on Wed Jun 26 2002 - 14:28:43 CDT