Systems Engineering and RDBMS

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
SELECT OBJECT_TYPE,OBJECT_NAME
FROM user_objects;

CREATE OR REPLACE TRIGGER TEST_TRIG
BEFORE UPDATE OF OBJECT_TYPE ON TEST FOR EACH ROW
BEGIN
: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);
END;
/

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.

update test
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.

Resources:

  • PL/SQL Reference – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: