[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
