Re: Get the name of a trigger
Date: 1997/11/09
Message-ID: <346d176d.132088663_at_newshost>#1/1
On Fri, 07 Nov 1997 16:29:23 -0800, Bureau IIB2 Nantes Tour Bretagne <vd006_at_dial.oleane.com> wrote:
>Hello every one,
>I'm looking for a method to get the name of a trigger in a PL/SQL
>module.
>Is it possible ?
>Please help.
In 7.3 and up yes, prior to that, no. In 7.3 they stored triggers as compiled objects. This made it so that the call stack would have the name of the compiled object in it. we can inspect the call stack to see who called us. In 7.2 and before, a trigger would always just show up as 'anonymous block' in the call stack -- no name.
here is an example that works in 7.3 and up. You have to parse the dbms_utility.format_call_stack to see who called you but it will always be the second line in the output.
create table trigger_test ( x int );
create or replace procedure trigger_test_proc
as
begin
dbms_output.put_line( dbms_utility.format_call_stack );
end;
/
create or replace trigger trigger_test
after insert or update or delete on trigger_test
begin
trigger_test_proc;
end;
/
set serveroutput on
insert into trigger_test values ( 1 );
SQL> Insert into trigger_test values ( 1 );
- PL/SQL Call Stack ----- object line object handle number name 8023370c 4 procedure SCOTT.TRIGGER_TEST_PROC 8014bae4 2 SCOTT.TRIGGER_TEST <<<---- Here it is...
SQL> begin
2 insert into trigger_test values ( 1 );
3 end;
4 /
----- PL/SQL Call Stack -----
object line object
handle number name
8023370c 4 procedure SCOTT.TRIGGER_TEST_PROC 8014bae4 2 SCOTT.TRIGGER_TEST <<<---- Here it is 8013ef84 2 anonymous block --------------------------------------------------------
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Nov 09 1997 - 00:00:00 CET