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 |
![]() |
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 |
![]() |
.... 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 |
![]() |