Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Question Unanswered: CREATE TRIGGER problems

    Hi all, first post here.

    I am trying to get a trigger created using SPUFI. I'm either having a simple syntax issue that I can't figure out, or worse, I am trying to use syntax that is not allowable. Any help would be appreciated.

    The trigger first checks for the existence of a row in the target table. If the row does not exist, an insert is done. If it does exist, an update is done.

    CREATE TRIGGER F3696DBC.ZUDCLG1X
    AFTER UPDATE OF STATUS
    ON F3696DBC.TIC_CLLGO_REQ
    REFERENCING OLD AS OLDROW NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    VALUES CASE
    WHEN (NEWROW.STATUS IN ('I', 'G', 'S', 'T', 'X')
    AND NOT EXISTS (SELECT CLLGO_REQ_ID
    FROM F3696DBC.TIC_CLLGO_EXCP
    WHERE CLLGO_REQ_ID = NEWROW.CLLGO_REQ_ID))
    THEN
    INSERT INTO F3696DBC.TIC_CLLGO_EXCP
    (CLLGO_REQ_ID,
    CODE,
    PARM_TXT,
    STTS_CD,
    DLV_MTHD_CD,
    PRN_MTHD_CD,
    UPDT_TS,
    UPDT_OPER_ID)
    VALUES
    (NEWROW.CLLGO_REQ_ID,
    NEWROW.CODE,
    NEWROW.PARAMETERS,
    NEWROW.STATUS,
    NEWROW.DLV_MTHD_CD,
    NEWROW.PRN_MTHD_CD,
    NEWROW.UPDT_TS,
    NEWROW.UPDT_OPER_ID);

    WHEN (NEWROW.STATUS IN ('I', 'G', 'S', 'T', 'X')
    AND EXISTS (SELECT CLLGO_REQ_ID
    FROM F3696DBC.TIC_CLLGO_EXCP
    WHERE CLLGO_REQ_ID = NEWROW.CLLGO_REQ_ID) )
    THEN
    UPDATE F3696DBC.TIC_CLLGO_EXCP
    SET CODE = NEWROW.CODE,
    PARM_TXT = NEWROW.PARAMETERS,
    STTS_CD = NEWROW.STATUS,
    DLV_MTHD_CD = NEWROW.DLV_MTHD_CD,
    PRN_MTHD_CD = NEWROW.PRN_MTHD_CD,
    UPDT_TS = NEWROW.UPDT_TS,
    UPDT_OPER_ID = NEWROW.UPDT_OPER_ID
    WHERE CLLGO_REQ_ID = NEWROW.CLLGO_REQ_ID;
    END;
    END#
    ---------+
    DSNT404I SQLCODE = 098, WARNING: A DYNAMIC SQL STATEMENT ENDS WITH A SEMICOLON
    DSNT418I SQLSTATE = 01568 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHTOKR SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 0 0 0 -1 3319 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
    X'00000CF7' X'00000000' SQL DIAGNOSTIC INFORMATION
    DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED
    SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE -199,
    SQLSTATE 42601, AND MESSAGE TOKENS INTO,END
    DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 506 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'000001FA' X'00000000' X'00000000' X'FFFFFFFF'
    X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION


    Thanks!

    ScottF

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Did you try to use IF instead of CASE?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2007
    Posts
    2
    That is actually a really good suggestion, but I just got word that our offshore partners were able to figure out a different way to code the SQL and get it to work. Thanks to all who looked at this!

    Here's the updated code for anyone who is interested.

    CREATE TRIGGER F3696DBC.ZUDCLG1B
    AFTER UPDATE
    OF STATUS
    ON F3696DBC.TIC_CLLGO_REQ
    REFERENCING NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL
    WHEN (NEWROW.STATUS IN ('I', 'G', 'S', 'T', 'X')
    AND EXISTS (SELECT CLLGO_REQ_ID
    FROM F3696DBC.TIC_CLLGO_EXCP
    WHERE CLLGO_REQ_ID = NEWROW.CLLGO_REQ_ID))
    BEGIN ATOMIC
    UPDATE F3696DBC.TIC_CLLGO_EXCP
    SET CODE = NEWROW.CODE,
    PARM_TXT = NEWROW.PARAMETERS,
    STTS_CD = NEWROW.STATUS,
    DLV_MTHD_CD = NEWROW.DLV_MTHD_CD,
    PRN_MTHD_CD = NEWROW.PRN_MTHD_CD,
    UPDT_TS = NEWROW.UPDT_TS,
    UPDT_OPER_ID = NEWROW.UPDT_OPER_ID
    WHERE CLLGO_REQ_ID = NEWROW.CLLGO_REQ_ID;END;

    WHEN (NEWROW.STATUS IN ('I', 'G', 'S', 'T', 'X')
    AND NOT EXISTS (SELECT CLLGO_REQ_ID
    FROM F3696DBC.TIC_CLLGO_EXCP
    WHERE CLLGO_REQ_ID = NEWROW.CLLGO_REQ_ID))
    BEGIN ATOMIC
    INSERT INTO F3696DBC.TIC_CLLGO_EXCP
    (CLLGO_REQ_ID,CODE,PARM_TXT,
    STTS_CD,DLV_MTHD_CD,PRN_MTHD_CD,
    UPDT_TS,UPDT_OPER_ID)
    VALUES (NEWROW.CLLGO_REQ_ID,NEWROW.CODE,
    NEWROW.PARAMETERS,NEWROW.STATUS,NEWROW.DLV_MTHD_CD ,
    NEWROW.PRN_MTHD_CD,NEWROW.UPDT_TS,
    NEWROW.UPDT_OPER_ID);END

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    First and foremost: you didn't tell us which error message you got. Also, which version of DB2 are you using?

    The thing is that the original trigger definition had a VALUES ... in it. First, you did not consume the result of the VALUES clause, so DB2 could happily optimize it away. Next, the CASE - as you used it - is an expression and not a statement. You may want to use a CASE statement or an IF statement instead, as n_i suggested. And finally, what you try to achieve is available using the MERGE statement. So your approach is a kludge these days: http://publib.boulder.ibm.com/infoce...lref/rmerg.htm
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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