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
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.
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';
