Since you defined your trigger as after insert, there is no "OLD" row. This
one works for me:

CREATE TRIGGER CALCMOD
AFTER INSERT ON NEIL
REFERENCING new row AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE NEIL SET MODULUS = MOD(n.ID,11)
WHERE n.ID = ID ;
END;



The information contained in this e-mail message and any attachments is confidential and intended only for the addressee(s). If you are not an addressee, you may not copy or disclose the information, or act upon it, and you should delete it entirely from your email system. Please also notify the sender that you received this e-mail in error.
-----Original Message-----
From: Burrows Neil
[mailto:db2udbdba-ezmlmshield-x61685666.[Email address protected]
Sent: Thursday, July 22, 2004 1:20 PM
To: LazyDBA Discussion
Subject: DB2 on MVS trigger

Hello.
I am attempting to write a trigger that is triggered on an insert to update
the inserted row with the result of a modulus calculation, which I intend to
use as a partitioning key for a child table.

Here is a table I used for testing a trigger
CREATE TABLE TST.NEIL
(
ID INTEGER NOT NULL,
MODULUS INTEGER NOT NULL WITH DEFAULT 11
)
IN TST.TSTTBSP ;

Here is the trigger:

CREATE TRIGGER CALCMOD
AFTER INSERT ON TST.NEIL
REFERENCING OLD AS OLD
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE TST.NEIL SET MODULUS = MOD(OLD.ID,11)
WHERE OLD.ID = ID ;
END ;

When I try to create the trigger, I get the following

CREATE TRIGGER CALCMOD AFTER INSERT ON TST.NEIL REFERENCING OLD AS OLD FOR
EAC
H ROW MODE DB2SQL BEGIN ATOMIC UPDATE TST.NEIL SET MODULUS = MOD(OLD.ID,11)
WH
ERE OLD.ID = ID















------------------------ E R R O R M E S S A G E S
-------------------------
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT

BE LEGAL ARE:

DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 0 0 0 -1 173 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

X'000000AD' X'00000000' SQL DIAGNOSTIC INFORMATION


What am I doing wrong? Any help would be much appreciated.

Neil Burrows
Data Architect
tel 303 397 5623



The information in this electronic mail message is sender's business
Confidential and may be legally privileged. It is intended solely for the
addressee(s). Access to this Internet electronic mail message by anyone
else is unauthorized. If you are not the intended recipient, any
disclosure, copying, distribution or any action taken or omitted to be taken
in reliance on it is prohibited and may be unlawful.
The sender believes that this E-mail and any attachments were free of any
virus, worm, Trojan horse, and/or malicious code when sent. This message and
its attachments could have been infected during transmission. By reading
the message and opening any attachments, the recipient accepts full
responsibility for taking protective and remedial action about viruses and
other defects. Galileo International is not liable for any loss or damage
arising in any way from this message or its attachments.




---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: db2udbdba-[Email address protected]

DB2 & UDB email list listserv db2-l LazyDBA home page