I am getting following error while executing the trigger, pls let me know if anyone knows how to resolve the error.


SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION
NUMBER : 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS -,TABLE STATEMENT , . SCROLL INSENSITIVE SENSITIVE
ASENSITIVE NO

Here is the trigger,

--#SET TERMINATOR ~

CREATE TRIGGER CWSRCAL0
AFTER INSERT ON CLAIM_TRANSACTION
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.CTX_TRS_TYPE_CD < 90)
BEGIN ATOMIC
DECLARE WS-COUNT INTEGER;
SET WS-COUNT =
(SELECT (*)
FROM CLAIM_SUPPORT_1_V1 CS01_COV_DESC
WHERE CS01_COV_DESC.CS01_TABLE_ID = 'C280'
AND CS01_COV_DESC.CS01_CODE =
(SELECT COVDESC.CCP_COV_MOD_CD
FROM CLM_COV_PKG_V1 COVDESC
WHERE COVDESC.CCP_CLAIM_NBR = N.CTX_CLAIM_NBR
AND COVDESC.CCP_PKG_TYPE_CD = N.CTX_COV_TYPE_CD));
IF WS-COUNT = 0 THEN
INSERT INTO CLM_ACTIVITY_LOG_V
(CAL_CLAIM_NBR
,CAL_ACY_TYPE_CD
,CAL_SORT_TS
,CAL_OBJECT_ID
,CAL_ORT_CLT_ID
,CAL_ACTIVITY_DES
,CAL_ACTIVITY_AMT
,CAL_CLN_CLT_ID)
SELECT
N.CTX_CLAIM_NBR
,'CTX'
,N.CTX_SORT_TS
,CLAIM.CLM_CSR_CLAIM_NBR
,ORIGINATOR.SEC_USR_CLT_ID
,RTRIM(CHAR(( 'Added ' ||
RTRIM(CS01_FIN_TYPE.CS01_CODE_DES )||' '||
RTRIM(CS01_TRANS_TYPE.CS01_CODE_DES ) ||' '||
RTRIM(CS01_COVERAGE_TYPE.CS01_CODE_DES ) ), 100))
,N.CTX_TRS_AMT
,N.CTX_CLIENT_ID
FROM
CLAIM_TAB_V1 CLAIM
,SEC_USRS_V ORIGINATOR
,CLAIM_SUPPORT_1_V1 CS01_COVERAGE_TYPE
,CLAIM_SUPPORT_1_V1 CS01_FIN_TYPE
,CLAIM_SUPPORT_1_V1 CS01_TRANS_TYPE
WHERE
(N.CTX_CLAIM_NBR = CLAIM.CLM_CLAIM_NBR)
AND (N.CTX_COV_TYPE_CD = CS01_COVERAGE_TYPE.CS01_CODE
AND CS01_COVERAGE_TYPE.CS01_TABLE_ID IN ('C039','C169'))
AND (N.CTX_FIN_TYPE_CD = CS01_FIN_TYPE.CS01_CODE
AND CS01_FIN_TYPE.CS01_TABLE_ID='C046')
AND (N.CTX_TRS_TYPE_CD = INTEGER(CS01_TRANS_TYPE.CS01_CODE)
AND CS01_TRANS_TYPE.CS01_TABLE_ID='C044')
AND (N.CTX_ENTRY_OPR_ID = ORIGINATOR.SEC_USR_ID)
ELSE
INSERT INTO CLM_ACTIVITY_LOG_V
(CAL_CLAIM_NBR
,CAL_ACY_TYPE_CD
,CAL_SORT_TS
,CAL_OBJECT_ID
,CAL_ORT_CLT_ID
,CAL_ACTIVITY_DES
,CAL_ACTIVITY_AMT
,CAL_CLN_CLT_ID)
SELECT
N.CTX_CLAIM_NBR
,'CTX'
,N.CTX_SORT_TS
,CLAIM.CLM_CSR_CLAIM_NBR
,ORIGINATOR.SEC_USR_CLT_ID
,RTRIM(CHAR(( 'Added ' ||
RTRIM(CS01_FIN_TYPE.CS01_CODE_DES )||' '||
RTRIM(CS01_TRANS_TYPE.CS01_CODE_DES ) ||' '||
RTRIM(CS01_COVERAGE_TYPE.CS01_CODE_DES ) ||' '||
RTRIM(CS01_COV_DESC.CS01_CODE_DES ) ), 100))
,N.CTX_TRS_AMT
,N.CTX_CLIENT_ID
FROM
CLAIM_TAB_V1 CLAIM
,SEC_USRS_V ORIGINATOR
,CLAIM_SUPPORT_1_V1 CS01_COVERAGE_TYPE
,CLAIM_SUPPORT_1_V1 CS01_FIN_TYPE
,CLAIM_SUPPORT_1_V1 CS01_TRANS_TYPE
,CLAIM_SUPPORT_1_V1 CS01_COV_DESC
WHERE
(N.CTX_CLAIM_NBR = CLAIM.CLM_CLAIM_NBR)
AND (N.CTX_COV_TYPE_CD = CS01_COVERAGE_TYPE.CS01_CODE
AND CS01_COVERAGE_TYPE.CS01_TABLE_ID IN ('C039','C169'))
AND (N.CTX_FIN_TYPE_CD = CS01_FIN_TYPE.CS01_CODE
AND CS01_FIN_TYPE.CS01_TABLE_ID='C046')
AND (N.CTX_TRS_TYPE_CD = INTEGER(CS01_TRANS_TYPE.CS01_CODE)
AND CS01_TRANS_TYPE.CS01_TABLE_ID='C044')
AND (N.CTX_ENTRY_OPR_ID = ORIGINATOR.SEC_USR_ID)
AND (CS01_COV_DESC.CS01_TABLE_ID ='C280'
AND CS01_COV_DESC.CS01_CODE =
(SELECT COVDESC.CCP_COV_MOD_CD
FROM CLM_COV_PKG_V1 COVDESC
WHERE COVDESC.CCP_CLAIM_NBR = CLAIM.CLM_CLAIM_NBR
AND COVDESC.CCP_PKG_TYPE_CD = N.CTX_COV_TYPE_CD))
END-IF;
END

~

--#SET TERMINATOR ;

COMMIT ;

May be COUNT was lost...
SET WS-COUNT =
(SELECT COUNT(*)

Thanks for the suggestion. But still getting the same error.

And (n.ctx_entry_opr_id = originator.sec_usr_id);
else

and covdesc.ccp_pkg_type_cd = n.ctx_cov_type_cd));
end-if;

Still the same error. Is it the way the variable is declared?

Ah! WS-COUNT ==> WS_COUNT

I realized

Should it be like given below?

DECLARE WS_COUNT INTEGER;
SET WS_COUNT =

Please study (DB2) SQL basics.

First of all, read the manual "DB2 SQL Reference".

And build such a trigger step-by-step, i.e. start with an empty body, then add 1 simple statement, then expand. After each step, test the trigger - until the full functionality is in place.