If you need some advice on the general use of triggers, I'd suggest reading the following Tom Kyte article :-
https://blogs.oracle.com/connect/post/the-trouble-with-triggers
However, as there are situations where the use of triggers is justified, or if you are maintaining an application which makes use of triggers, it is possible to add instrumentation to triggers so that their activity can be monitored in the same way as procedures and functions.
There are 3 methods of instrumenting triggers, each of which has its advantages and disadvantages.
NOTE - To run the examples here against your installation of BMC_DEBUG, your user must have the CREATE TRIGGER privilege, which is not necessary for general operation. You'll also need to create this test table :-
SQL> CREATE TABLE trigger_demo (id NUMBER NOT NULL, descr VARCHAR2(500) NOT NULL);
Table created.
Method 1 - Using the BMC_DEBUG.SAFE_MSG procedure
This is the recommended way of instrumenting triggers. The BMC_DEBUG.SAFE_MSG procedure is a cut-down version of the MSG procedure, which checks the value of the DEBUG LEVEL parameter for the currently executing procedure, and writes the passed message to the BMC_DEBUG_LOG table if the DEBUG LEVEL is >= the msg level.
It does not call DBMS_OUTPUT to write messages to the screen, nor does it manipulate the message store, or call DBMS_APPLICATION_INFO.
The key point to remember about trigger instrumentation is that triggers could be invoked by SQL statements run directly inside SQL*Plus, or from applications which are not instrumented, so it is possible that BMC_DEBUG procedures could be invoked when BMC_DEBUG.BEGINCALL has not been called to initialise the call stack.
Using SAFE_MSG ensures that this is never a problem, as if the call stack is not initialised, SAFE_MSG will do nothing
Here is an example trigger which uses SAFE_MSG to log a message :-
SQL> CREATE TRIGGER trigtest1_trigger
BEFORE INSERT ON trigger_demo FOR EACH ROW
BEGIN
bmc_debug.safe_msg(msglevel => 3,
msg => 'Inserting descr '||:new.descr,
program_unit_type => 'TRIGGER',
program_unit_name => $$PLSQL_UNIT,
flag => 'T');
EXCEPTION
WHEN OTHERS THEN
bmc_error.error_handle(force_package => 'TRIGGER', force_procedure => $$PLSQL_UNIT);
RAISE;
END;
/
Trigger created.
If we then create and run a procedure which has a DEBUG LEVEL of 3 or greater, and INSERTs a row into the test table :-
SQL> CREATE OR REPLACE PROCEDURE trigtest1_proc IS
BEGIN
bmc_debug.begincall('PROCEDURE',$$PLSQL_UNIT,5); -- Force debug level 5
INSERT INTO trigger_demo (ID,DESCR) values (1,'WIDGET');
bmc_debug.endcall('PROCEDURE',$$PLSQL_UNIT);
END trigtest1_proc;
/
Procedure created.
SQL> exec trigtest1_proc;
PROCEDURE.TRIGTEST1_PROC begins (Forced debug level 5)
PROCEDURE.TRIGTEST1_PROC ends
PL/SQL procedure successfully completed.
If we then query BMC_DEBUG_LOG to see what was logged :-
SQL> SELECT log_date,sid,package_name,procedure_name,msg_level,flag,msg FROM bmc_debug_log
WHERE procedure_name like 'TRIGTEST1%' ORDER BY log_date;
LOG_DATE SID PACKAGE_NAME PROCEDURE_NAME MSG_LEVEL FLA MSG
-------------------- ------- ------------- -------------------- ---------- --- -----------------------------------
15-NOV-2014 12.24.59 124 PROCEDURE TRIGTEST1_PROC 5 B PROCEDURE.TRIGTEST1_PROC begins (Forced debug level 5)
15-NOV-2014 12.24.59 124 TRIGGER TRIGTEST1_TRIGGER 3 T Inserting descr WIDGET
15-NOV-2014 12.24.59 124 PROCEDURE TRIGTEST1_PROC 5 E PROCEDURE.TRIGTEST1_PROC ends
So the call to SAFE_MSG inside the trigger refers to the DEBUG LEVEL set by the procedure which caused the trigger to fire. If that is high enough, the message passed to SAFE_MSG is inserted into the log table.
As the trigger does not call BEGINCALL or modify the call stack in any way, the call to BMC_ERROR.ERROR_HANDLE in the trigger has to use the FORCE_PACKAGE and FORCE_PROCEDURE parameters to override the values on the top of the call stack in case an exception is encountered, as this will allow the correct program unit name to be included in the error trace.
Method 2 - Using the BMC_DEBUG.MSG procedure
This method uses the regular BMC_DEBUG.MSG call to write the message to the log table. While this has some advantages over SAFE_MSG, it has one large disadvantage which we'll discuss.
Here is an example trigger which uses BMC_DEBUG.MSG to log a message :-
SQL> DROP TRIGGER trigtest1_trigger;
Trigger dropped.
SQL> CREATE TRIGGER trigtest2_trigger
BEFORE INSERT ON trigger_demo FOR EACH ROW
BEGIN
bmc_debug.msg(msglevel => 3,
msg => 'Inserting descr '||:new.descr,
force_package => 'TRIGGER',
force_procedure => $$PLSQL_UNIT,
flag => 'T');
EXCEPTION
WHEN OTHERS THEN
bmc_error.error_handle(force_package => 'TRIGGER', force_procedure => $$PLSQL_UNIT);
RAISE;
END;
/
Trigger created.
If we then create and run a procedure which has a DEBUG LEVEL of 3 or greater, and INSERTs a row into the test table (making sure DBMS_OUTPUT is enabled) :-
SQL> CREATE OR REPLACE PROCEDURE trigtest2_proc IS
BEGIN
bmc_debug.begincall('PROCEDURE',$$PLSQL_UNIT,5); -- Force debug level 5
INSERT INTO trigger_demo (ID,DESCR) values (2,'SPROCKET');
bmc_debug.endcall('PROCEDURE',$$PLSQL_UNIT);
END trigtest2_proc;
/
Procedure created.
SQL> exec trigtest2_proc;
PROCEDURE.TRIGTEST2_PROC begins (Forced debug level 5)
Inserting descr SPROCKET
PROCEDURE.TRIGTEST2_PROC ends
If we then query BMC_DEBUG_LOG to see what was logged :-
SQL> SELECT log_date,sid,package_name,procedure_name,msg_level,flag,msg FROM bmc_debug_log
WHERE procedure_name like 'TRIGTEST2%' ORDER BY log_date;
LOG_DATE SID PACKAGE_NAME PROCEDURE_NAME MSG_LEVEL FLA MSG
---------------------- ------- ------------- -------------------- ---------- --- -----------------------------------------
15-NOV-2014 12.27.13 124 PROCEDURE TRIGTEST2_PROC 5 B PROCEDURE.TRIGTEST2_PROC begins (Forced debug level 5)
15-NOV-2014 12.27.13 124 TRIGGER TRIGTEST2_TRIGGER 3 T Inserting descr SPROCKET
15-NOV-2014 12.27.13 124 PROCEDURE TRIGTEST2_PROC 5 E PROCEDURE.TRIGTEST2_PROC ends
So the end result is the same as with SAFE_MSG, but there was one difference. The message generated by the trigger was written to the screen by DBMS_OUTPUT, as that is something that BMC_DEBUG.MSG does.
While this is convenient, there is one drawback with using MSG with triggers. As we mentioned in Method 1, triggers can be fired by SQL statements which are not running inside instrumented code, so it is possible for the BMC_DEBUG call stack to not be initialised when the trigger runs. In this case, BMC_DEBUG.MSG will ALWAYS write the message to the log table at a message level of 0. For example, if in SQL*Plus we directly INSERT a row into the test table :-
SQL> INSERT INTO trigger_demo VALUES (9,'TEST');
BMC_DEBUG.MSG called with empty stack : Inserting descr TEST
1 row created.
We can see the message from the trigger being output to the screen, along with a warning that MSG has been invoked when the call stack is empty (and the message will have been written to the log table).
This is the key difference between MSG and SAFE_MSG - In an uninitialised environment, MSG always writes the message to the log table, whereas SAFE_MSG never does. If you were to create triggers which called MSG and they were triggered by SQL which was running directly from SQL*Plus, there would be no way to control the number of messages they would write to the log table.
Method 3 - Make each trigger its own program unit
This is the most powerful, but also the most problematic method of trigger instrumentation.
Rather than using the DEBUG LEVEL which is in effect in the triggering procdure, as Methods 1 and 2 do, it is also possible to call BEGINCALL and ENDCALL inside the trigger, which gives it the same flexibility with regard to parameter value assignment as a regular procedure.
Here is an example trigger which calls BEGINCALL and ENDCALL, as well as MSG :-
SQL> DROP TRIGGER trigtest1_trigger;
Trigger dropped.
SQL> DROP TRIGGER trigtest2_trigger;
Trigger dropped.
SQL> CREATE TRIGGER trigtest3_trigger
BEFORE INSERT ON trigger_demo FOR EACH ROW
BEGIN
bmc_debug.begincall('TRIGGER',$$PLSQL_UNIT);
bmc_debug.msg(3,'Inserting descr '||:new.descr);
bmc_debug.endcall('TRIGGER',$$PLSQL_UNIT);
EXCEPTION
WHEN OTHERS THEN
bmc_error.error_handle();
RAISE;
END;
/
Trigger created.
As this trigger calls BEGINCALL, we can set its DEBUG LEVEL via a parameter table entry, just as we would do for a procedure :-
SQL> INSERT INTO bmc_debug_parameter (param_name, param_value, inherit, package_name, procedure_name)
VALUES ('DEBUG LEVEL',3,'N','TRIGGER','TRIGTEST3_TRIGGER');
1 row created.
SQL> commit;
Commit complete.
Now that we have specified the DEBUG LEVEL for the trigger, we can invoke the trigger by any means (e.g a SQL statement directly from SQL*Plus), not just from an instrumented procedure :-
SQL> INSERT INTO trigger_demo VALUES (5,'THING');
Inserting descr THING
1 row created.
SQL> SELECT log_date,sid,package_name,procedure_name,msg_level,msg FROM bmc_debug_log
WHERE procedure_name = 'TRIGTEST3_TRIGGER';
LOG_DATE SID PACKAGE_NAME PROCEDURE_NAME MSG_LEVEL MSG
------------------------------ ---------- -------------------- -------------------- ---------- ------------------------------
15-NOV-2014 08.43.49 132 TRIGGER TRIGTEST3_TRIGGER 3 Inserting descr THING
The main advantage of this method of instrumenting triggers is that message logging for triggers can be independently enabled and disabled using BMC_DEBUG_PARAMETER entries, as their DEBUG LEVELs can be specified on a per-trigger basis. It is also possible to add a parameter table entry to control all triggers. Consider these parameter table entries :-
PARAM_NAME PARAM_VALUE INHERIT PACKAGE_NAME PROCEDURE_NAME SID SERIAL# USERNAME COMMENTS
------------- ----------- ------- -------------------- --------------------- --- ------- -------- ----------------------------
DEBUG LEVEL 7 N TRIGGER INSERT_ORDER_TRIG
DEBUG LEVEL 9 N TRIGGER UPDATE_CUST_TRIG FRED
DEBUG LEVEL 3 N TRIGGER Default for Triggers
So here we have two DEBUG LEVEL parameter table entries for specific triggers, but the third entry is applying a DEBUG LEVEL to all triggers whose DEBUG LEVEL is not set via a more specific parameter table entry.
The main disadvantage of this method of trigger instrumentation is performance. As the trigger is calling BEGINCALL and ENDCALL, this is an overhead which will be encountered for every DML statement which causes the trigger to fire.
This is especially problematic with FOR EACH ROW triggers, which fire for every row which the DML statement processes.
Imagine if you had a BEFORE UPDATE .. FOR EACH ROW trigger which was instrumented with BEGINCALL and ENDCALL, and you ran an UPDATE statement on the underlying table which updated 1 million rows. Your UPDATE statement would effectively invoke BEGINCALL and ENDCALL 1 million times, along with anything else the trigger body decided to do ....
Needless to say, this is by far the largest performance impact that any form of instrumentation could have on your code, so think long and hard before instrumenting triggers this way.