Please Help: Insert generating -803 in Trigger

Hi Everebody:
I have a table:

CREATE TABLE CROSS_REFERENCE
(ROW# INTEGER NOT NULL
,KEY_WORD CHAR(16) NOT NULL
,QUERY_DESCR VARCHAR(330) NOT NULL
,PRIMARY KEY (ROW#,KEY_WORD));

It is a cross reference table to my CATALOG Table based on key words.
I am trying to create a tigger. Every time when i insert a row in CATALOG
Table corresponding
rows will be inserted in Cross referenvce table depending of key words.
I tested trigger body first:

I want to Insert in Catalog table following row:
INSERT INTO NEW_CATALOG
VALUES
('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',13,'HOW TO TRANSFER
JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY');

There are 2 key words in this row: JOIN and SUBQUERY.
Last keys in groups before testing trigger body:
SELECT KEY_WORD,MAX(ROW#) AS LAST_GROUP_NUM
FROM CROSS_REFERENCE
WHERE KEY_WORD IN('JOIN','SUBSEL')
GROUP BY KEY_WORD;

KEY_WORD LAST_GROUP_NUM
---------------- --------------------------------------
JOIN 64
SUBSEL 13

Trigger body:
INSERT INTO CROSS_REFERENCE
WITH T1 (QUERY_DESCR) AS
(VALUES( 'HOW TRANSFER SUBSELECT IN JOIN')),
T2(ItemName,MAX_ROW#) AS
(SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
FROM CROSS_REFERENCE
GROUP BY STRIP(KEY_WORD)),
T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
(SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS QTY_USED
FROM T1,T2
WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE(STRIP (QUERY_DESCR),
ITEMNAME,''))) > 0
GROUP BY ITEMNAME,MAX_ROW#)
SELECT MAX_ROW# + 1,ITEM_NAME ,'SUBSL' ||' ' || CHAR(13)||' '||QUERY_DESCR
FROM T3,T1;

DB20000I The SQL command completed successfully.

Same query After succsesfull INSERT:

SELECT KEY_WORD,MAX(ROW#) AS LAST_GROUP_NUM
FROM CROSS_REFERENCE
WHERE KEY_WORD IN('JOIN','SUBSEL')
GROUP BY KEY_WORD;

KEY_WORD LAST_GROUP_NUM
---------------------- ----------------------------------
JOIN 65
SUBSEL 14

Now i am tesing with the Trigger:


CREATE TRIGGER CROSS_REFF_TRIG
AFTER INSERT
ON NEW_CATALOG
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
INSERT INTO CROSS_REFERENCE
WITH T1 (QUERY_DESCR) AS
(SELECT n.QUERY_DESC FROM NEW_CATALOG),
T2(ItemName,MAX_ROW#) AS
(SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
FROM CROSS_REFERENCE
GROUP BY STRIP(KEY_WORD)),
T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
(SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS QTY_USED
FROM T1,T2
WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE(STRIP
(QUERY_DESCR),ITEMNAME,''))) > 0
GROUP BY ITEMNAME,MAX_ROW#)
SELECT MAX_ROW# + 1,ITEM_NAME ,n.GROUP_ID ||' ' ||CHAR(QUERY#)||'
'||QUERY_DESCR FROM T3,T1;

DB20000I The SQL command completed successfully.

trigger event:

INSERT INTO NEW_CATALOG
VALUES
('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',14
,'HOW TO TRANSFER JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY');

SQLCODE "-803", SQLSTATE "23505" and message tokens "1|LENY.CROSS_REFERENCE".

SQLSTATE=09000
Why -803. MAX_ROW# + 1 in this query always create unique key.
Thank's in advance Leny.G

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200808/1

I Just added Fetch first 1 row only to the trigger and it is working,
inserting only one row.
But i need more then one insert. Does it mean I cannot have multible inserts
in the trigger?

lenygold wrote:
>Hi Everebody:
>I have a table:
>
>CREATE TABLE CROSS_REFERENCE
>(ROW# INTEGER NOT NULL
>,KEY_WORD CHAR(16) NOT NULL
>,QUERY_DESCR VARCHAR(330) NOT NULL
>,PRIMARY KEY (ROW#,KEY_WORD));
>
>It is a cross reference table to my CATALOG Table based on key words.
>I am trying to create a tigger. Every time when i insert a row in CATALOG
>Table corresponding
>rows will be inserted in Cross referenvce table depending of key words.
>I tested trigger body first:
>
>I want to Insert in Catalog table following row:
>INSERT INTO NEW_CATALOG
>VALUES
>('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',13,'HOW TO TRANSFER
>JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY');
>
>There are 2 key words in this row: JOIN and SUBQUERY.
>Last keys in groups before testing trigger body:
>SELECT KEY_WORD,MAX(ROW#) AS LAST_GROUP_NUM
> FROM CROSS_REFERENCE
> WHERE KEY_WORD IN('JOIN','SUBSEL')
> GROUP BY KEY_WORD;
>
>KEY_WORD LAST_GROUP_NUM
>---------------- --------------------------------------
>JOIN 64
>SUBSEL 13
>
>Trigger body:
>INSERT INTO CROSS_REFERENCE
> WITH T1 (QUERY_DESCR) AS
> (VALUES( 'HOW TRANSFER SUBSELECT IN JOIN')),
> T2(ItemName,MAX_ROW#) AS
> (SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
> FROM CROSS_REFERENCE
> GROUP BY STRIP(KEY_WORD)),
> T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
> (SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS QTY_USED
> FROM T1,T2
> WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE(STRIP (QUERY_DESCR),
>ITEMNAME,''))) > 0
> GROUP BY ITEMNAME,MAX_ROW#)
> SELECT MAX_ROW# + 1,ITEM_NAME ,'SUBSL' ||' ' || CHAR(13)||' '||QUERY_DESCR
>FROM T3,T1;
>
>DB20000I The SQL command completed successfully.
>
>Same query After succsesfull INSERT:
>
>SELECT KEY_WORD,MAX(ROW#) AS LAST_GROUP_NUM
> FROM CROSS_REFERENCE
> WHERE KEY_WORD IN('JOIN','SUBSEL')
> GROUP BY KEY_WORD;
>
>KEY_WORD LAST_GROUP_NUM
>---------------------- ----------------------------------
>JOIN 65
>SUBSEL 14
>
>Now i am tesing with the Trigger:
>
>CREATE TRIGGER CROSS_REFF_TRIG
>AFTER INSERT
>ON NEW_CATALOG
>REFERENCING NEW AS n
>FOR EACH ROW
>MODE DB2SQL
>INSERT INTO CROSS_REFERENCE
> WITH T1 (QUERY_DESCR) AS
> (SELECT n.QUERY_DESC FROM NEW_CATALOG),
> T2(ItemName,MAX_ROW#) AS
> (SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
> FROM CROSS_REFERENCE
> GROUP BY STRIP(KEY_WORD)),
> T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
> (SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS QTY_USED
> FROM T1,T2
> WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE(STRIP
>(QUERY_DESCR),ITEMNAME,''))) > 0
> GROUP BY ITEMNAME,MAX_ROW#)
> SELECT MAX_ROW# + 1,ITEM_NAME ,n.GROUP_ID ||' ' ||CHAR(QUERY#)||'
>'||QUERY_DESCR FROM T3,T1;
>
>DB20000I The SQL command completed successfully.
>
>trigger event:
>
>INSERT INTO NEW_CATALOG
>VALUES
>('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',14
>,'HOW TO TRANSFER JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY');
>
>SQLCODE "-803", SQLSTATE "23505" and message tokens "1|LENY.CROSS_REFERENCE".
>
>SQLSTATE=09000
>Why -803. MAX_ROW# + 1 in this query always create unique key.
>Thank's in advance Leny.G

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200808/1

Never mind. I was able to resolve this problem by using udf + sp
execute_immediate and
execute this insert dynamicly.

lenygold wrote:
>I Just added Fetch first 1 row only to the trigger and it is working,
>inserting only one row.
>But i need more then one insert. Does it mean I cannot have multible inserts
>in the trigger?
>
>>Hi Everebody:
>>I have a table:
>[quoted text clipped - 98 lines]
>>Why -803. MAX_ROW# + 1 in this query always create unique key.
>>Thank's in advance Leny.G

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200808/1