Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 15: Creating and Using Triggers

Home Contents Index Master Index New Book

Tracing Triggered Actions

If a triggered action does not behave as you expect, place it in an SPL routine, and use the SPL TRACE statement to monitor its operation. Before starting the trace, you must direct the output to a file with the SET DEBUG FILE TO statement. The following example shows TRACE statements that have been added to the SPL routine items_pct. The SET DEBUG FILE TO statement directs the trace output to the file /usr/mydir/trig.trace. The TRACE ON statement begins tracing the statements and variables within the routine.

CREATE PROCEDURE items_pct(mac CHAR(3))

DEFINE tp MONEY;   

DEFINE mc_tot MONEY;

DEFINE pct DECIMAL;

SET DEBUG FILE TO '/usr/mydir/trig.trace';

TRACE 'begin trace';

TRACE ON;

LET tp = (SELECT SUM(total_price) FROM items);

LET mc_tot = (SELECT SUM(total_price) FROM items

	WHERE manu_code = mac);

LET pct = mc_tot / tp;

IF pct > .10 THEN

	RAISE EXCEPTION -745;

END IF

TRACE OFF;

END PROCEDURE;   



CREATE TRIGGER items_ins

INSERT ON items

REFERENCING NEW AS post_ins

FOR EACH ROW(EXECUTE PROCEDURE items_pct (post_ins.manu_code));

The following example shows sample trace output from the items_pct routine as it appears in the file /usr/mydir/trig.trace. The output reveals the values of routine variables, routine arguments, return values, and error codes.

trace expression :begin trace

trace on

expression:

  (select (sum total_price)

    from items)

evaluates to $18280.77 ;

let  tp = $18280.77

expression:

  (select (sum total_price)

    from items

    where (= manu_code, mac))

evaluates to $3008.00 ;

let  mc_tot = $3008.00

expression:(/ mc_tot, tp)

evaluates to 0.16

let  pct = 0.16

expression:(> pct, 0.1)

evaluates to 1

expression:(- 745)

evaluates to -745

raise exception :-745, 0, ''

exception : looking for handler

SQL error = -745 ISAM error = 0  error string =  = ''

exception : no appropriate handler

For more information on using the TRACE statement to diagnose logic errors in SPL routine, see Chapter 14, "Creating and Using SPL Routines."

Informix Guide to SQL: TutorialChapter 15: Creating and Using Triggers Home Contents Index Master Index New Book

Tracing Triggered Actions

Informix Guide to SQL: Tutorial
Chapter 15: Creating and Using Triggers

Home Contents Index Master Index New Book