How do I write a DB2 trigger for the below. I have started a BEFORE and AFTER trigger below... What problems does it have?

TIDMRMST unique index is MATERIAL_REQUEST.

TIDYCXTR definition:
SELECT NAME, COLTYPE, LENGTH
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'PPQ10'
AND TBNAME = 'TIDYCXTR'
ORDER BY COLNO
WITH UR
---------+---------+---------+-------
NAME COLTYPE LENGTH
---------+---------+---------+-------
TOUCHPOINT_ID CHAR 10 - 'CHAN_REC' rec type
TOUCHPOINT_KEY CHAR 200 - comma delimited, variable length
TIME_STAMP TIMESTMP 10

Example TIDYCXTR data (SUBSTR(TOUCHPOINT_KEY,2,9) = MATERIAL_REQUEST):
TOUCHPOINT_ID TOUCHPOINT_KEY
---------+---------+---------+---------+---------+---------+---
CHAN_REQ ,00565446CEP512C 3 ,U,TIGILDTM/EPIUSER
CHAN_REQ ,00811016BET194P 0 , ,TIGIALLC/EPIUSER
/\EPIMR\MRNDC

OBJECTIVE:
I want to use the TIDYCXTR MATERIAL_REQUEST (substring 2,9 above) to select MR_STATUS from table TIDMRMST where
TIDMRMST.MATERIAL_REQUEST = TIDYCXTR.TOUCHPOINT_KEY(substring 2,9).
Then I want to append a comma + MR_STATUS to the end of TOUCHPOINT_KEY
field (append to end of same record).


BEFORE DB2 Trigger:

CREATE TRIGGER test
BEFORE INSERT ON TIDYCXTR
REFERENCING NEW AS NEWYCXTR
FOR EACH ROW MODE DB2SQL
WHEN (NEWYCXTR.TOUCHPOINT_ID = 'CHAN_REQ')

SubSelect MR_STATUS From TIDMRMST
WHERE (TIDMRMST.MATERIAL_REQUEST = NEWYCXTR.
SUBSTR(TOUCHPOINT_KEY,2,9))

INSERT TIDYCXTR (TOUCHPOINT_ID, TOUCHPOINT_KEY, TIME_STAMP)
VALUES (NEWYCXTR.TOUCHPOINT_ID ,NEWYCXTR.TOUCHPOINT_KEY || ‘,’ ||
MR_STATUS, NEWYCXTR.TIME_STAMP)


AFTER DB2 Trigger:

CREATE TRIGGER test
AFTER INSERT ON TIDYCXTR
REFERENCING OLD AS OLDYCXTR
NEW AS NEWYCXTR
FOR EACH ROW MODE DB2SQL
WHEN (OLDYCXTR.TOUCHPOINT_ID = 'CHAN_REQ')

SubSelect MR_STATUS From TIDMRMST
WHERE (TIDMRMST.MATERIAL_REQUEST = OLDYCXTR.
SUBSTR(TOUCHPOINT_KEY,2,9)

UPDATE OLDYCXTR
WHERE ((OLDYCXTR.TOUCHPOINT_ID = NEWYCXTR.TOUCHPOINT_ID) AND
(OLDYCXTR.TOUCHPOINT_KEY = NEWYCXTR.TOUCHPOINT_KEY) AND
(OLDYCXTR.TIME_STAMP = NEWYCXTR.TIME_STAMP))
SET (NEWYCXTR.TOUCHPOINT_KEY = OLDYCXTR.TOUCHPOINT_KEY || ‘,’ ||
MR_STATUS)

From what I've read, this BEFORE trigger is closer. Using SET instead of INSERT...:

CREATE TRIGGER test
BEFORE INSERT ON TIDYCXTR
REFERENCING NEW AS NEWYCXTR
FOR EACH ROW MODE DB2SQL
WHEN (NEWYCXTR.TOUCHPOINT_ID = 'CHAN_REQ')
(SELECT MR_STATUS FROM TIDMRMST
WHERE (TIDMRMST.MATERIAL_REQUEST = NEWYCXTR.
SUBSTR(TOUCHPOINT_KEY,2,9)))
SET NEWYCXTR.TOUCHPOINT_KEY = NEWYCXTR.TOUCHPOINT_KEY || ',' || MR_STATUS