Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32

    Exclamation Unanswered: Calling a procedure in a trigger URGENT!!

    Hi all,

    I'm trying to call a procedure modifies sql data in a trigger after update, but when i'm executing the update that shots the trigger this error message occurs:

    SQL0723N An error occurred in a triggered SQL statement in trigger
    "APN.TR_APN_CTB_A". Information returned for the error includes SQLCODE
    "-746", SQLSTATE "57053" and message tokens
    "APNCTB.#SP_INSER|SQL05010710405030|READ|AP". SQLSTATE=09000

    Someone knows what's happening here.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Alairj
    Information returned for the error includes SQLCODE
    "-746", SQLSTATE "57053" and message tokens
    "APNCTB.#SP_INSER|SQL05010710405030|READ|AP".
    Code:
    C:\>db2 ? sql0746
    
    
    SQL0746N Routine "<routine-name>" (specific name
              "<specific-name>") violated nested SQL statement rules
              when attempting to perform operation "<operation>" on
              table "<table-name>".
    
    Explanation:
    
     The routine "<routine-name>" (specific name
    "<specific-name>") attempted to perform the operation
    "<operation>" on table "<table-name>". This operation conflicts
    with other uses of the table by either the application, or a
    routine invoked directly or indirectly from that application.
    
     If the operation is "READ", then the table "<table-name>" is
    currently being written to by either the application or another
    routine.
    
     If the operation is "MODIFY", then the table "<table-name>" is
    already being read from or written to by the application or another
    routine.
    
     If "<table-name>" references an explain table and the statement
    that receives this error message is either a PREPARE statement or
    an EXECUTE IMMEDIATE statement, then a conflict occurred when
    inserting explain information into the explain table.
    
    User Response:
    
     The operation may be successful if retried. Redesign either the
    application or the routine to avoid the conflict.
    
     If the conflict occurred when inserting explain information for
    a dynamic statement, then disable explain for dynamic statements
    and try the PREPARE statement or EXECUTE IMMEDIATE statement
    again.
    
     sqlcode :  -746
    
     sqlstate :  57053

  3. #3
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    I saw this error message but i can't find a way to solve my problem with this message, because in my case i'm making a insert in a table but i didn't use this table in another application or routine.

    If you find a solution please tell me.

  4. #4
    Join Date
    May 2004
    Posts
    11
    Hi

    Does the trigger do anything else, except make just single stored procedure call ?

    I'm meaning that might it be that the trigger and stored procedure are accessing the same table, and therefore it might produce access conflict between them (check the links 3 rules in "The following rules are applied:")

    Data conflicts when procedures read from or write to tables:
    http://publib.boulder.ibm.com/infoce...d/c0009164.htm


    Antti

  5. #5
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    I think i found what's happening...

    Db2 don't permits, in a trigger, that you make a insert in a table and then calls a procedure that makes a insert in another table that contains a Foreign Key with this table. I think db2 suppouse that the two tables are the same or some thing like that.

    The error messege stops when i dropped the Foreign Key. If someone has another solution please tell me.

Posting Permissions

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