Calling a procedure in a trigger URGENT!!

Alairj · #1 (**permalink**) 01-07-05, 13:11

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

n_i · #2 (**permalink**) 01-07-05, 14:51

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

Alairj · #3 (**permalink**) 01-10-05, 07:15

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.

antti · #4 (**permalink**) 01-10-05, 09:01

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

Alairj · #5 (**permalink**) 01-11-05, 05:38

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech