I am puzzling on this trigger problem.



Here is a trigger body



create trigger esadbm.trg_updstratinits1

AFTER UPDATE OF STRAT_INIT_LVL_1 ON ESADBM.WH_STRATEGIC_INITS

REFERENCING NEW as n

FOR EACH ROW

MODE DB2SQL

BEGIN ATOMIC

CALL ESADBM.sp_updinitpath(n.strat_init_id);

END





A very simple stored procedure which I cut off from a long procedure to
be able to identify the problem



CREATE PROCEDURE ESADBM.sp_updinitpath (p_strat_init_id INTEGER )

SPECIFIC sp_updinitpath

DYNAMIC RESULT SETS 1

LANGUAGE SQL

BEGIN

begin

declare bb integer;

SELECT strat_init_id

INTO bb

FROM ESADBM.wh_strategic_inits

WHERE strat_init_id =p_strat_init_id;

end;

END





Here is Error Msg when trigger fired



"During SQL processing it returned: SQL0723N An error occurred in a
trigger SQL statement in trigger "ESADBM.Trg_updst".

Information returned for the error includes SQLCODE "-746", SQLState
"57053"



SQLCODE -746 represents "THE SQL STATEMENT IN AN EXTERNAL FUNCTION,
TRIGGER, OR IN STORED PROCEDURE name VIOLATES THE NESTING SQL
RESTRICTION"



I am really in puzzling about this explanation. Could somebody explain
to me and tell me what is wrong with my trigger?



Thanks. I have more than hundred triggers require call procedures.






























DB2 & UDB email list listserv db2-l LazyDBA home page