CODEWITHSUNDEEP
plsqloracle11g
jasim
jasim

Reputation: 288

Stored procedures with triggers oracle 11g

i have a store procedure which is called by 4 different trigger is it possible to get trigger's information like name when the procedure is executed.

i can do it by passing a extra parameter to the procedure, is there any other option? i am using oracle 11g. Thanks

Upvotes: 1

Views: 1319

Answers (2)

Marco Baldelli
Marco Baldelli

Reputation: 3728

You could use the function dbms_utility.format_call_stack which is documented here:

SQL> set serveroutput on
SQL> create table test_table (n number);

Table created.

SQL> create or replace procedure test_procedure as
  2  begin
  3     dbms_output.put_line(dbms_utility.format_call_stack);
  4  end;
  5  /

Procedure created.

SQL> create or replace trigger test_trigger_insert
  2  before insert on test_table
  3  for each row
  4  begin
  5     test_procedure;
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger test_trigger_delete
  2  before delete on test_table
  3  for each row
  4  begin
  5     test_procedure;
  6  end;
  7  /

Trigger created.

SQL> rem direct execution of the procedure
SQL> exec test_procedure;
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x2bc2f1ca0         3  procedure YOUR_SCHEMA.TEST_PROCEDURE
0x31a218568         1
anonymous block


PL/SQL procedure successfully completed.

SQL> rem procedure called by trigger test_trigger_insert
SQL> insert into test_table (n) values (42);
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x2bc2f1ca0         3  procedure YOUR_SCHEMA.TEST_PROCEDURE
0x2adc40fb0         2
YOUR_SCHEMA.TEST_TRIGGER_INSERT


1 row created.

SQL> rem procedure called by trigger test_trigger_delete
SQL> delete from test_table where n = 42;
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x2bc2f1ca0         3  procedure YOUR_SCHEMA.TEST_PROCEDURE
0x2f10ffd28         2
YOUR_SCHEMA.TEST_TRIGGER_DELETE


1 row deleted.

Upvotes: 2

Oprea Alexandru Cristian
Oprea Alexandru Cristian

Reputation: 116

If your trigger is called My_Trigger you can try this:

SELECT Trigger_type, Triggering_event, Table_name
   FROM USER_TRIGGERS
   WHERE Trigger_name = 'My_Trigger';

Upvotes: 1

Related Questions