SEO

#1 (**permalink**) 02-27-2008, 01:07 PM

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

#2 (**permalink**) 02-27-2008, 01:07 PM

On Oct 8, 9:22 am, Massimiliano Campagnoli <m...@paoloastori.com>
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

#3 (**permalink**) 02-27-2008, 01:07 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

BOM QUERY IN TRIGGER