TRIGGER SEQUENCE #'S

Craig Reiter

TRIGGER SEQUENCE #'S
I'm trying to create a trigger to update a column with a sequence #(s/390
DB2 v6). When I use a COUNT(*) the trigger is created without error but
when I use the MAX() function on the mentioned sequencing column I get the
error -118. The trigger works on NT.


--------------------------- Command entered ----------------------------
CREATE TRIGGER MFT.TI3COMP
AFTER INSERT
ON MFT.EV_COMPONENT
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN (NEW.STATUS_KEY < 0)
UPDATE MFT.EV_COMPONENT
SET STATUS_KEY =
(SELECT COUNT(*) FROM MFT.EV_COMPONENT WHERE EVENT_KEY = NEW.EVENT_KEY AND COMPONENT_KEY = NEW.COMPONENT_KEY)
WHERE EVENT_KEY = NEW.EVENT_KEY AND COMPONENT_KEY = NEW.COMPONENT_KEY AND STATUS_KEY = NEW.STATUS_KEY;


------------------------------------------------------------------------
DB20000I The SQL command completed successfully.



--------------------------- Command entered ----------------------------
DROP TRIGGER MFT.TI3COMP RESTRICT

------------------------------------------------------------------------
DB20000I The SQL command completed successfully.



--------------------------- Command entered ----------------------------
CREATE TRIGGER MFT.TI3COMP
AFTER INSERT
ON MFT.EV_COMPONENT
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN (NEW.STATUS_KEY < 0)
UPDATE MFT.EV_COMPONENT
SET STATUS_KEY =
(SELECT MAX(STATUS_KEY) FROM MFT.EV_COMPONENT WHERE EVENT_KEY = NEW.EVENT_KEY AND COMPONENT_KEY = NEW.COMPONENT_KEY)
WHERE EVENT_KEY = NEW.EVENT_KEY AND COMPONENT_KEY = NEW.COMPONENT_KEY AND STATUS_KEY = NEW.STATUS_KEY;


------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0969N There is no message text corresponding to SQL error "-20100" in the
message file on this workstation. The error was returned from module "DSNXOGP
" with original tokens "2 -118 42902 ". SQLSTATE=56059



Terry Purcell

Re: TRIGGER SEQUENCE #'S
(in response to Craig Reiter)
Craig,

The problem (as far as I can tell) is that the update statement within your
trigger is self-referencing. Although valid in non-OS/390 DB2, an update
statement cannot be self-referencing until DB2 OS/390 V7.

/snip/
UPDATE MFT.EV_COMPONENT
SET STATUS_KEY =
(SELECT MAX(STATUS_KEY) FROM MFT.EV_COMPONENT
/snip/

Regards
Terry Purcell
[login to unmask email]
Yevich Lawson & Associates
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Craig Reiter
Sent: Thursday, December 14, 2000 11:35 AM
To: [login to unmask email]
Subject: TRIGGER SEQUENCE #'S


I'm trying to create a trigger to update a column with a sequence #(s/390
DB2 v6). When I use a COUNT(*) the trigger is created without error but
when I use the MAX() function on the mentioned sequencing column I get the
error -118. The trigger works on NT.


--------------------------- Command entered ----------------------------
CREATE TRIGGER MFT.TI3COMP
AFTER INSERT
ON MFT.EV_COMPONENT
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN (NEW.STATUS_KEY < 0)
UPDATE MFT.EV_COMPONENT
SET STATUS_KEY =
(SELECT COUNT(*) FROM MFT.EV_COMPONENT WHERE EVENT_KEY = NEW.EVENT_KEY
AND COMPONENT_KEY = NEW.COMPONENT_KEY)
WHERE EVENT_KEY = NEW.EVENT_KEY AND COMPONENT_KEY = NEW.COMPONENT_KEY AND
STATUS_KEY = NEW.STATUS_KEY;


------------------------------------------------------------------------
DB20000I The SQL command completed successfully.



--------------------------- Command entered ----------------------------
DROP TRIGGER MFT.TI3COMP RESTRICT

------------------------------------------------------------------------
DB20000I The SQL command completed successfully.



--------------------------- Command entered ----------------------------
CREATE TRIGGER MFT.TI3COMP
AFTER INSERT
ON MFT.EV_COMPONENT
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN (NEW.STATUS_KEY < 0)
UPDATE MFT.EV_COMPONENT
SET STATUS_KEY =
(SELECT MAX(STATUS_KEY) FROM MFT.EV_COMPONENT WHERE EVENT_KEY =
NEW.EVENT_KEY AND COMPONENT_KEY = NEW.COMPONENT_KEY)
WHERE EVENT_KEY = NEW.EVENT_KEY AND COMPONENT_KEY = NEW.COMPONENT_KEY AND
STATUS_KEY = NEW.STATUS_KEY;


------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0969N There is no message text corresponding to SQL error "-20100" in
the
message file on this workstation. The error was returned from module
"DSNXOGP
" with original tokens "2 -118 42902 ". SQLSTATE=56059