Results 1 to 2 of 2

Thread: DB2 trigger

  1. #1
    Join Date
    Jun 2006
    Posts
    2

    Unanswered: DB2 trigger

    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)

  2. #2
    Join Date
    Jun 2006
    Posts
    2

    More current BEFORE trigger statement

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •