Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    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.

    Thanks
    Last edited by larmstrong; 11-19-10 at 18:38.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check the explanation for SQLCODE -440 and act accordingly.

  3. #3
    Join Date
    Nov 2010
    Posts
    3
    I did not understand the actions described in the -440 description, since when the call is coded without the delete no error is generated. Could you explain.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I can't explain something I don't see. Since you chose not to show your actual code, I'm afraid you'll have to find the explanation yourself.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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).
    Dave

  6. #6
    Join Date
    Nov 2010
    Posts
    3
    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.

Posting Permissions

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