having a script like this

CREATE TRIGGER DAS_XX                                  
 AFTER INSERT ON TESTTABLE                             
 REFERENCING NEW AS N                                  
 FOR EACH ROW                                          
 MODE DB2ROW                                           
 BEGIN                                                 
  DECLARE STM CHAR(500) ;                              
  DECLARE CURRLIB CHAR(10) ;                           
  SET CURRLIB = 'TESTDB' ;                             
  SET SCHEMA CURRLIB ;                                 
  SET STM = 'CALL SUBJECT_ONINSERT' CONCAT '(?,?,?,?)';      
  PREPARE S FROM STM ;                                 
  EXECUTE S USING                                      
  N.TESTFLD1 , N.TESTFLD2 , N.TESTFLD3 , N.TESTFLD4 ;  
  END ;                                               

when I run it I get this error:
SQL0206 - Column or global variable CURRLIB not found

if I remember correctly I have always used, in last years, script like this to generate triggers 
or I am wrong ?

Thanks
Marco

Il 21.10.2016 17.55, Marco Franchini ha scritto:
> having a script like this
>
> CREATE TRIGGER DAS_XX
>  AFTER INSERT ON TESTTABLE
>  REFERENCING NEW AS N
>  FOR EACH ROW
>  MODE DB2ROW
>  BEGIN
>   DECLARE STM CHAR(500) ;
>   DECLARE CURRLIB CHAR(10) ;
>   SET CURRLIB = 'TESTDB' ;
>   SET SCHEMA CURRLIB ;
>   SET STM = 'CALL SUBJECT_ONINSERT' CONCAT '(?,?,?,?)';
>   PREPARE S FROM STM ;
>   EXECUTE S USING
>   N.TESTFLD1 , N.TESTFLD2 , N.TESTFLD3 , N.TESTFLD4 ;
>   END ;
>
> when I run it I get this error:
> SQL0206 - Column or global variable CURRLIB not found
>
> if I remember correctly I have always used, in last years, script like this to generate triggers
> or I am wrong ?
The set statement is evelueted as CURRLIB were a field name. As I 
recall, the only SET statements that you can include in the body are:

SET CURRENT DEBUG
SET CURRENT degree
SET descriptor
SET ENCRYPTION password
SET path
SET CURRENT_SCHEMA
SET transaction

Also, you can only use DECLARE as:

DECLARE GLOBAL TEMPORARY table

Further more. I don't think you can use a cursor inside a trigger body.
So I guess, but I'm not sure, that using:

BEGIN
SET CURRENT_SCHEMA 'TESTDB';
EXECUTE IMMEDIATE CALL SUBJECT_ONINSERT (N.TESTFLD1, N.TESTFLD2, 
N.TESTFLD3, N.TESTFLD4);
END

would succseed.

.... strange behaviour! it's related to set schema. the reference is 
mentioning that set schema only applies to dynamic SQL. But using set schema 
'someliteral' is possible and set schema = 'aliteral' and set current schema 
.... as well. Looks to me like some of the bugfeatures of this not ready 
designed SQL stuff. (BTW: the rather new redbooks ar rather poor too, 
containing not working examples.

D*B


"Marco Franchini" <marco.franchini.it@gmail.com> schrieb im Newsbeitrag 
news:49f50ffb-3922-4272-811c-531d4e249588@googlegroups.com...
> having a script like this
>
> CREATE TRIGGER DAS_XX
> AFTER INSERT ON TESTTABLE
> REFERENCING NEW AS N
> FOR EACH ROW
> MODE DB2ROW
> BEGIN
>  DECLARE STM CHAR(500) ;
>  DECLARE CURRLIB CHAR(10) ;
>  SET CURRLIB = 'TESTDB' ;
>  SET SCHEMA CURRLIB ;
>  SET STM = 'CALL SUBJECT_ONINSERT' CONCAT '(?,?,?,?)';
>  PREPARE S FROM STM ;
>  EXECUTE S USING
>  N.TESTFLD1 , N.TESTFLD2 , N.TESTFLD3 , N.TESTFLD4 ;
>  END ;
>
> when I run it I get this error:
> SQL0206 - Column or global variable CURRLIB not found
>
> if I remember correctly I have always used, in last years, script like 
> this to generate triggers
> or I am wrong ?
>
> Thanks
> Marco