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

Did you try to use IF instead of CASE?

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

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