By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

BOM QUERY IN TRIGGER

P: n/a
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

Oct 8 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Oct 8 '07 #2

P: n/a
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
Oct 9 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.