Unanswered: Create DB2 Trigger with DELETE and CALL actions
DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED
SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 10 SQLCODE
-440, SQLSTATE 42884, AND MESSAGE TOKENS PROCEDURE,SP
DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOSTP SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 1286145 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'0013A001' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Generated when attempting to create trigger with delete and call action. Can this be done?
CREATE TRIGGER schema1.trigger AFTER INSERT ON schema1.tablex
REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN ( USER
NOT IN ( 'ABC' , 'XYZ' ) AND ( LENGTH ( N.KEY_ID )
< 35 ) AND ( UPPER ( N.columny ) IN ( 'SUCCESS' , 'CANCEL' ,
'DOWNSTREAMBIZFAILED' , 'FAILED' ) ) ) BEGIN ATOMIC
DELETE FROM schema2.tableq WHERE TBL_ID = ( DECIMAL ( N.KEY ) ) ;
CALL schema2.sp ( N.KEY_ID ) ;
When Trigger created with just the DELETE or just the CALL, no error is generated.
so, you mean that the trigger does get created when you have just the CALL to your SP? To me it looks like the error message is telling you that the SP in the trigger does not exist or doesn't exist with compatible argument(s).
Yes, that is correct, only when the CALL is proceeded by the DELETE is the error generated. I used the version with only the CALL to verify correctness of the stored procedure definition. The -440 appears to be misleading.