Home » SQL & PL/SQL » SQL & PL/SQL » Reference to Program that Fired the Trigger
Reference to Program that Fired the Trigger [message #401435] Tue, 05 May 2009 00:56 Go to next message
megabyte
Messages: 2
Registered: May 2009
Junior Member
Hi everyone,

Greetings!

Is there anyway for a trigger to know which program fired it?

Like for instance, sample_table has an insert trigger associated to it called sample_table_on_insert. If sample_program inserts a new row on sample_table, is there anyway for sample_table_on_insert to know that it was sample_program who did the insert?

Hope you could help me out.

Thanks in advance...
Re: Reference to Program that Fired the Trigger [message #401438 is a reply to message #401435] Tue, 05 May 2009 01:01 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Trigger format is...
Create or replace trigger sample_table_on_insert
after insert on sample_table  ---Here you specify the table name
for each row
begin
......
end;


Hope cleared your doubt.
Thanks,

[Updated on: Tue, 05 May 2009 01:02]

Report message to a moderator

Re: Reference to Program that Fired the Trigger [message #401439 is a reply to message #401435] Tue, 05 May 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query v$session in your trigger.

Regards
Michel
Re: Reference to Program that Fired the Trigger [message #401445 is a reply to message #401438] Tue, 05 May 2009 01:17 Go to previous messageGo to next message
megabyte
Messages: 2
Registered: May 2009
Junior Member
Thanks for the fast reply. Okay, allow me to clarify my question.

Is there anyway for a trigger to know which program fired it? For example:

Create or replace trigger sample_table_on_insert
after insert on sample_table ---Here you specify the table name
for each row
begin
if form_who_fired_trigger = 'sample_program' then
raise_application_error (-20810, 'sample_program is not allowed to modify this table');
end if;
end;


Hope you could help me out. Thanks!! Smile
Re: Reference to Program that Fired the Trigger [message #401449 is a reply to message #401445] Tue, 05 May 2009 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 05 May 2009 08:09
Query v$session in your trigger.

Regards
Michel

You can also set client_info in your application to indicate which part of the program you're in.

Regards
Michel

Re: Reference to Program that Fired the Trigger [message #401450 is a reply to message #401435] Tue, 05 May 2009 01:46 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Create or replace trigger sample_table_on_insert
after insert on sample_table ---Here you specify the table name
for each row
begin
SELECT program 
into v_program
FROM v$session
where sid=....;

if v_program = 'sample_program' then
raise_application_error (-20810, 'sample_program is not allowed to modify this table');
end if;
end;

[Updated on: Tue, 05 May 2009 01:46]

Report message to a moderator

Re: Reference to Program that Fired the Trigger [message #401466 is a reply to message #401450] Tue, 05 May 2009 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@ora_2007,

Once again, your post didn't add anything to mine (in fact it contains less information) and is in violation of the guidelines which stated to post hint or point to the right direction, and is disrespect for what I posted.

In addition, if OP is not able to come from what I posted to what you posted, he will not be able to come to what you posted to a working trigger.

Regards
Michel
Re: Reference to Program that Fired the Trigger [message #401470 is a reply to message #401435] Tue, 05 May 2009 02:24 Go to previous messageGo to next message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
Hi,

You may use dbms_utility.format_call_stack inside the trigger code

In the call stack first object would be the Trigger
the second one should be the procedure/function which inserted
into sample_table.

You will need to extract the procedure/function name out of the call stack.

Thanks,
Idris
Re: Reference to Program that Fired the Trigger [message #401474 is a reply to message #401470] Tue, 05 May 2009 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try what you posted?
What if it is not a PL/SQL procedure that executes the statement?

Regards
Michel
Re: Reference to Program that Fired the Trigger [message #401475 is a reply to message #401470] Tue, 05 May 2009 02:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/tkyte/who_called_me/index.html
Re: Reference to Program that Fired the Trigger [message #401477 is a reply to message #401470] Tue, 05 May 2009 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a small test case:
SQL> create table t (col integer);

Table created.

SQL> create or replace trigger my_trg before insert on t
  2  begin
  3    dbms_output.put_line (DBMS_UTILITY.FORMAT_CALL_STACK);
  4  end;
  5  /

Trigger created.

SQL> create or replace procedure p as
  2  begin
  3    insert into t values(0);
  4  end;
  5  /

Procedure created.

SQL> exec p;
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
1EA86D34         2  MICHEL.MY_TRG
1EBB
871C         3  procedure MICHEL.P
22491B34         1  anonymous block


PL/SQL procedure successfully completed.

SQL> insert into t values(0);
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
1EA86D34         2  MICHEL.MY_TRG


1 row created.

As you can see you cannot know the end program (here SQL*Plus) that does the insert.

Regards
Michel
Re: Reference to Program that Fired the Trigger [message #401603 is a reply to message #401477] Tue, 05 May 2009 13:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
-- table and trigger (using who_called_me procedure by Tom Kyte):
SCOTT@orcl_11g> create table t (col integer)
  2  /

Table created.

SCOTT@orcl_11g> create or replace trigger my_trg
  2    before insert on t
  3  declare
  4    v_owner	    varchar2 (30);
  5    v_name	    varchar2 (30);
  6    v_lineno     number;
  7    v_caller_t   varchar2 (30);
  8    v_program    varchar2 (30);
  9  begin
 10    who_called_me (v_owner, v_name, v_lineno, v_caller_t);
 11    select program
 12    into   v_program
 13    from   v$session sess
 14    where  sess.audsid = userenv ('sessionid');
 15    dbms_output.put_line
 16  	 ('called from:  ' ||
 17  	  v_program || ' ' ||
 18  	  NVL (v_caller_t, 'SQL') || ' ' ||
 19  	  v_owner    || '.' ||
 20  	  v_name     );
 21  end my_trg;
 22  /

Trigger created.

SCOTT@orcl_11g> show errors
No errors.


-- insert from procedure executed from SQL*Plus:
SCOTT@orcl_11g> create or replace procedure p as
  2  begin
  3    insert into t values (0);
  4  end p;
  5  /

Procedure created.

SCOTT@orcl_11g> exec p
called from:  sqlplus.exe PROCEDURE SCOTT.P

PL/SQL procedure successfully completed.


-- insert from anonymous pl/sql block called from SQL*Plus:
SCOTT@orcl_11g> begin
  2    insert into t values (0);
  3  end;
  4  /
called from:  sqlplus.exe ANONYMOUS BLOCK .

PL/SQL procedure successfully completed.


-- insert using SQL only called from SQL*Plus:
SCOTT@orcl_11g> insert into t
  2  values (0)
  3  /
called from:  sqlplus.exe SQL .

1 row created.


-- insert from another trigger:
SCOTT@orcl_11g> create or replace trigger my_trig2
  2    after delete on t
  3  begin
  4    insert into t values (0);
  5  end my_trig2;
  6  /

Trigger created.

SCOTT@orcl_11g> delete from t
  2  /
called from:  sqlplus.exe TRIGGER SCOTT.MY_TRIG2

3 rows deleted.

SCOTT@orcl_11g>

Re: Reference to Program that Fired the Trigger [message #401604 is a reply to message #401445] Tue, 05 May 2009 13:34 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The demo in my previous response shows that you can determine whether the insert that fired the trigger was from a procedure or an anonymous pl/sql block or from sql or from another trigger and you can determine whether it was from SQL*Plus or something else. However, if people are aware that there are restrictions based on these things, then they may get around them by copying things to other names. Some of these security issues and alternatives are discussed here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:561622956788
Previous Topic: Rank Signal
Next Topic: Formatting a number
Goto Forum:
  


Current Time: Thu Dec 08 22:31:24 CST 2016

Total time taken to generate the page: 0.14108 seconds