f



Trigger SQL #2

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
0
Marco
10/21/2016 3:55:17 PM
comp.sys.ibm.as400.misc 9219 articles. 3 followers. Post Follow

2 Replies
271 Views

Similar Articles

[PageSpeed] 7

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.
0
Dr
10/22/2016 6:39:33 AM
.... 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 

0
Dieter
10/22/2016 8:09:01 AM
Reply: