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
