I created on trigger as given below which works when a row is found in last select query but does not work when no row is found. Is there anyway I can handle the row nor found condition.


--#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
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

~

--#SET TERMINATOR ;

COMMIT ;