BOM QUERY IN TRIGGER

[Massimiliano Campagnoli]

I am not able to use a recursive query (BOM query) inside a trigger in
DB2 V7.2

Something like this:

CREATE TRIGGER my_trigger
AFTER INSERT ON my_table
REFERENCING NEW AS NRS
FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

IF (WITH RPL (LVL, CODART, CODLOT, CQGEN) AS
(SELECT 1, ROOT.CODART, ROOT.CODLOT, ROOT.CQGEN
FROM my_table ROOT WHERE ROOT.CQGEN = NRS.CQGEN

UNION ALL

SELECT PARENT.LVL+1, CHILD.CODART, CHILD.CODLOT, CHILD.CQGEN
FROM RPL PARENT, my_table CHILD
WHERE PARENT.CODLOT=CHILD.CQGEN AND PARENT.LEVEL < 20 )

SELECT COUNT(*) FROM RPL WHERE CODART LIKE '4NAC45%') 0

THEN SIGNAL SQLSTATE '70444';

END

[jefftyzzer]

On Oct 8, 9:22 am, Massimiliano Campagnoli <[e-mail]>
wrote:

I am not able to use a recursive query (BOM query) inside a trigger in
DB2 V7.2

Something like this:

CREATE TRIGGER my_trigger
AFTER INSERT ON my_table
REFERENCING NEW AS NRS
FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

IF (WITH RPL (LVL, CODART, CODLOT, CQGEN) AS
(SELECT 1, ROOT.CODART, ROOT.CODLOT, ROOT.CQGEN
FROM my_table ROOT WHERE ROOT.CQGEN = NRS.CQGEN

UNION ALL

SELECT PARENT.LVL+1, CHILD.CODART, CHILD.CODLOT, CHILD.CQGEN
FROM RPL PARENT, my_table CHILD
WHERE PARENT.CODLOT=CHILD.CQGEN AND PARENT.LEVEL < 20 )

SELECT COUNT(*) FROM RPL WHERE CODART LIKE '4NAC45%') 0

THEN SIGNAL SQLSTATE '70444';

END

Please include the error you're receiving.

--Jeff

[Serge Rielau]

CREATE TRIGGER my_trigger
AFTER INSERT ON my_table
REFERENCING NEW AS NRS
FOR EACH ROW MODE DB2SQL
WITH RPL (LVL, CODART, CODLOT, CQGEN) AS
(SELECT 1, ROOT.CODART, ROOT.CODLOT, ROOT.CQGEN
FROM my_table ROOT WHERE ROOT.CQGEN = NRS.CQGEN

UNION ALL

SELECT PARENT.LVL+1, CHILD.CODART, CHILD.CODLOT, CHILD.CQGEN
FROM RPL PARENT, my_table CHILD
WHERE PARENT.CODLOT=CHILD.CQGEN AND PARENT.LEVEL < 20 )

SELECT CAST(RAISE_ERROR('70444', '') FROM RPL WHERE CODART
LIKE '4NAC45%'

WITH can't be nested (it's part of select-statement, not fullselect)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab