|
From: Irene Yu Newsgroups: sybase.public.sqlanywhere.general Subject: Correlation error in trigger Date: 6 Nov 2010 01:17:46 -0800 Good day, Have a script being used in triggers in 3 tables and it works fine except for one table wheren it's giving an error "Correlation 'new_name' not found" Script: REFERENCING OLD AS old_name NEW AS new_name FOR EACH ROW Set @ls_sql_old = 'set @ls_old_char_value = convert(char(200), old_name.'+@ls_column_name + ');'; Set @ls_sql_new = 'set @ls_new_char_value = convert(char(200), new_name.'+@ls_column_name +');' ; Execute immediate @ls_sql_new; Execute immediate @ls_sql_old; The @ls_column_name holds the column name which is fetched from another table in a cursor. Error occurs in the execute immediate script. I have tried adding specific statements just right before the execute immediate script which will have the same statement at one point in both @ls_sql_new & @ls_sql_old statements, like: set @ls_old_char_value = convert(char(200), old_name.lastname); set @ls_new_char_value = convert(char(200), new_name.lastname); If cursor fetches @ls_column_name = 'lastname', it will have no problem running the execute immediate scripts. However on the next loop wherein @ls_column_name holds another column name, it will raise the error "Correlation 'new_name' not found" Using ASA 9.0.2.3044 Would greatly appreciate any insight. Thanks. |
|
This looks like a deficiency in version 9.0.2. The problem remains in 9.0.2.3575 and is gone in 10.0.1.3415. Here is a reproducible test case: CREATE TABLE t (
key_1 INTEGER NOT NULL PRIMARY KEY,
non_key_1 VARCHAR ( 100 ) NOT NULL,
non_key_2 VARCHAR ( 100 ) NOT NULL,
non_key_3 VARCHAR ( 100 ) NOT NULL );
INSERT t VALUES ( 1, 'aaa', 'bbb', 'ccc' );
COMMIT;
CREATE VARIABLE @ls_sql_new LONG VARCHAR;
CREATE VARIABLE @ls_new_char_value LONG VARCHAR;
CREATE VARIABLE @ls_column_name LONG VARCHAR;
CREATE TRIGGER tru_t BEFORE UPDATE ON t
REFERENCING OLD AS old_name NEW AS new_name
FOR EACH ROW
BEGIN
Set @ls_sql_new
= 'set @ls_new_char_value = convert(char(200), new_name.'
+ @ls_column_name
+ ');' ;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_sql_new = "', @ls_sql_new, '"' ) TO CONSOLE;
Execute immediate @ls_sql_new;
END;
SET @ls_column_name = 'non_key_2';
UPDATE t
SET non_key_1 = 'xxx',
non_key_2 = 'yyy'
WHERE key_1 = 1;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_column_name = "', @ls_column_name, '"' ) TO CONSOLE;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_new_char_value = "', @ls_new_char_value, '"' ) TO CONSOLE;
SET @ls_column_name = 'non_key_1';
UPDATE t
SET non_key_1 = 'ppp',
non_key_2 = 'qqq'
WHERE key_1 = 1;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_column_name = "', @ls_column_name, '"' ) TO CONSOLE;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_new_char_value = "', @ls_new_char_value, '"' ) TO CONSOLE;
Here are the test results... Adaptive Server Anywhere Database Engine Version 9.0.2.3575 Correlaton name 'new_name' not found SQLCODE=-142, ODBC 3 State="42S02" SQL Anywhere Personal Server Version 10.0.1.3415 DIAG 2010-11-07 05:26:16.390 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_2);" DIAG 2010-11-07 05:26:16.406 @ls_column_name = "non_key_2" DIAG 2010-11-07 05:26:16.406 @ls_new_char_value = "yyy" DIAG 2010-11-07 05:26:16.421 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_1);" DIAG 2010-11-07 05:26:16.421 @ls_column_name = "non_key_1" DIAG 2010-11-07 05:26:16.437 @ls_new_char_value = "ppp" SQL Anywhere Personal Server Version 11.0.1.2276 DIAG 2010-11-07 05:24:56.859 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_2);" DIAG 2010-11-07 05:24:56.875 @ls_column_name = "non_key_2" DIAG 2010-11-07 05:24:56.875 @ls_new_char_value = "yyy" DIAG 2010-11-07 05:24:56.890 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_1);" DIAG 2010-11-07 05:24:56.890 @ls_column_name = "non_key_1" DIAG 2010-11-07 05:24:56.906 @ls_new_char_value = "ppp" SQL Anywhere Personal Server Version 12.0.0.2589 DIAG 2010-11-07 05:23:06.593 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_2);" DIAG 2010-11-07 05:23:06.609 @ls_column_name = "non_key_2" DIAG 2010-11-07 05:23:06.609 @ls_new_char_value = "yyy" DIAG 2010-11-07 05:23:06.609 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_1);" DIAG 2010-11-07 05:23:06.625 @ls_column_name = "non_key_1" DIAG 2010-11-07 05:23:06.625 @ls_new_char_value = "ppp" |