Re: What does a trigger know?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/17
Message-ID: <337efeab.1901213@newshost>#1/1

wanna know the table the trigger is on
wanna know the owner/name of the trigger
wanna know if we are inserting/updating/deleting

Ok, to find out what table the trigger is on, if we know the trigger name and owner, we can figure that out. To get the trigger owner/name in a procedure, try the following in your procedure: (the call stack shows every thing called all the way back to the original anonymous block)

create or replace procedure proc3
as

    l_str varchar2(4096) default dbms_utility.format_call_stack;
    l_n1    number;
    l_n2    number;

begin

    l_n1 := instr( l_str, chr(10), -2 );
    l_n2 := instr( l_str, chr(10), -1 );
    l_str := substr( l_str, l_n1, l_n2-l_n1-1 );

dbms_output.put_line( l_str );
end;
/

drop table foobar;
create table foobar ( x number );

create or replace trigger foo_trigg
before insert or update or delete on foobar for each row
begin

proc3;
end;

So, that'll get us the owner/name and table (we can get the table from all_triggers or dba_triggers).

In the trigger itself, you have access to the booleans:

if ( inserting ) then
  ...
elsif ( updating ) then
  ...
elsif ( deleting ) then
  ...
end if;

so you can use those to figure out in the trigger whats happening..

On 16 May 1997 21:48:27 GMT, j4ychan_at_PROBLEM_WITH_INEWS_GATEWAY_FILE (Johnny Chan) wrote:

>Brian Gastineau (bgastine_at_giveblood.org) wrote:
>> Does a trigger have information on how it was envoked? For example, is
>> there a global variable (or other method) which tells a trigger which table
>> caused it to fire? Alternately, if the trigger knows its own name, I think
>> that a data dictionary table could be queried to get the table name. This
>> is all the information I need in the trigger now, but if other information
>> (example: insert, update, delete operation) is available in the same place,
>> I could see using that also.

>> My motivation for this question is to make a generic PL/SQL procedure which
>> is called from triggers on multiple tables. The processing accomplished
>> would depend on the table firing the trigger. I would prefer not to
>> hard-code the table name as a parameter to avoid manually verifying that
>> the table names in the trigger match the firing table.
>
>I thought about doing something like this a while ago myself but I gave
>up as there doesn't appear a trigger can pick that info up. The only
>pre-defined global variables are the :new and :old in a "before" triggers.
>
>Johnny Chan
>Independent Oracle Specialist
>

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 Received on Sat May 17 1997 - 00:00:00 CDT