| re: SQL0101N and SQL0723N - Recursive Triggers Should Not be Happening
Michael wrote:[color=blue]
> I want to insert a row into an ITEM table if certain SKUs are inserted.
> There are two triggers where each looks for a particular SKU and inserts the
> appropriate matching row in the same table. However when a record is
> inserted, the following error message occurs:
>
> Under DB2 UDB v7.2
> [IBM][CLI Driver][DB2/NT] SQL0101N The statement is too long or too
> complex. LINE NUMBER=2. SQLSTATE=54001
>
> Under DB2 UDB v8.1
> [IBM][CLI Driver][DB2/NT] SQL0723N An error occurred in a triggered SQL
> statement in trigger "DBA.ITEM_B". Information returned for the error
> includes SQLCODE "-101", SQLSTATE "54001" and message tokens "".
> SQLSTATE=09000
>
> Both messages point to a recursion of triggers. This seems to be confirmed
> by dropping either of the two triggers. The trouble is the condition for
> the triggers should never happen. I've provided sample SQL statements below
> to recreate the situation. Why is this happening? Thanks in advance.
>
> CREATE TABLE ITEM (
> ORDER_NO CHARACTER(10) NOT NULL,
> SKU_NO CHARACTER(10) NOT NULL,
> UNITS INTEGER NOT NULL
> )
> ;
>
> CREATE TRIGGER ITEM_A AFTER INSERT ON ITEM REFERENCING NEW AS N_ROW FOR EACH
> ROW MODE DB2SQL WHEN
> (
> n_row.sku_no = 'A1'
> )
> begin atomic
> INSERT INTO ITEM
> (ORDER_NO, SKU_NO, UNITS)
> VALUES (N_ROW.ORDER_NO, 'A2', 1);
> end
>
> CREATE TRIGGER ITEM_B AFTER INSERT ON ITEM REFERENCING NEW AS N_ROW FOR EACH
> ROW MODE DB2SQL WHEN
> (
> n_row.sku_no = 'B1'
> )
> begin atomic
> INSERT INTO ITEM
> (ORDER_NO, SKU_NO, UNITS)
> VALUES (N_ROW.ORDER_NO, 'B2', 1);
> end
>
> INSERT INTO ITEM
> (ORDER_NO, SKU_NO, UNITS)
> VALUES
> ('100', 'C1', 1);
>
>[/color]
The problem is not one of runtime (i.e. whether DB2 enters teh recursion.
It is about compile time. DB2 tries to expand all _possible_ recursions
16 levels deep.
So the one insert can trigger two inserts which can trigger two inserts.
All in all you end up with 32,000 _possible_ INSERTS. No wonder it blows.
In your example teh fix is simple:
Unify teh two INSERT statements by unifying the two triggers.
I.e. INSERT INTO item VALUES (<row>), (<row>);
This way there is no fan out and DB2 expands only for 16 inserts.
If your real trigger is a lot bigger you may still need to increase the
statement heap a bit, but it will be managable.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
| 
