Back to the basics: Debugging triggers using dbms_output
Posted by decipherinfosys on March 20, 2009
We can debug triggers just like we can debug any other stored programs (package, procedure or a function) using the dbms_output package. Let us start straight with an example. Connect using a SQL*Plus session and create the following table and trigger:
CREATE TABLE TEST1 AS
CREATE OR REPLACE TRIGGER TEST_TRIG
BEFORE UPDATE OF OBJECT_TYPE ON TEST FOR EACH ROW
:NEW.OBJECT_TYPE := UPPER(:NEW.OBJECT_TYPE);
DBMS_OUTPUT.PUT_LINE(‘Old Type = ‘ || :OLD.OBJECT_TYPE);
DBMS_OUTPUT.PUT_LINE(‘New Type = ‘ || :NEW.OBJECT_TYPE);
We have put debugging lines in the trigger it self just like we put it in procedure or function. Now run the following update statement.
set object_type = ‘view’
where object_name = ‘TEST’;
Do not forget to run “set serveroutput on” in order to see the debugging information. Upon successful update following information will be displayed on the screen.
Old Type = TABLE
New Type = VIEW
One thing to remember is that if debugging statements are put into ‘FOR EACH ROW’ trigger and if DML statement effects multiple rows, debug information will be displayed multiple times, once for each row.
And there are many tools like Toad or PL/SQL Developer which also facilitate easier debugging.
- PL/SQL Reference – here.