Why does "passthrough for remote_user;" cause the following error (ASA 220.127.116.1113)?
COMMIT/ROLLBACK not allowed within atomic operation SQLCODE=-267, ODBC 3 State="42000"
Here's the simplified situation:
create table table1 ( id integer not null default autoincrement, column1 integer, primary key (id)); create table table2 ( id integer not null default autoincrement, column1 integer, primary key (id));
Only table2 is part of a SQL Remote subscription
create trigger "trigger1" after update of column1 order 1 on table1 referencing new as new_val for each row begin passthrough for remote_user; execute immediate 'update table2 set column1 = ' || new_val.column1 || ' where id = ' || new_val.id; passthrough stop; end;
If I now execute "update table1 set column1 = 10 where id = 2" I get the error -267.
If the trigger only does the execute immediate, it works just fine. If the trigger does a "passthrough ONLY for remote_user" it returns the same error. So it almost looks like the passthrough call is causing this error!?
Passthrough operations, in this respect, are very similar to DDL (which are also not permitted inside of atomic operations) and are probably internally classified that way.
If I were to classify the PASSTHROUGH statement, I would have classified it as DCL "Data Control Language" in that it affects the SQL Remote "stream" in ways that are not classic DDL or DML. As such they should not be permitted inside of triggers. Either way ... this is not unexpected.
So be 'a shortened tree' no more.
There are other 'non-transactional' aspects to the use of PassThrough statements as well which can affect other scenarios. [there is a reason there are lots of known caveats affixed to this feature ... and why you always test before deploying]
answered 21 Mar '14, 11:31
Nick Elson S...
The Help should say "Side effects - Automatic commit" for the SQL Remote PASSTHROUGH statement. Here is a reproducible that proves the point:
BEGIN UPDATE parent SET non_key_1 = 123 WHERE key_1 = 1; COMMIT; SELECT @@VERSION, 'First SELECT', non_key_1 FROM parent WHERE key_1 = 1; UPDATE parent SET non_key_1 = 456 WHERE key_1 = 1; PASSTHROUGH FOR site0001; PASSTHROUGH STOP; ROLLBACK; SELECT @@VERSION, 'Second SELECT', non_key_1 FROM parent WHERE key_1 = 1; END; @@VERSION,'First SELECT',non_key_1 '18.104.22.16851','First SELECT','123' @@VERSION,'Second SELECT',non_key_1 '22.214.171.12451','Second SELECT','456'
answered 21 Mar '14, 14:56
In case PASSTHROUGH mode is not allowed within a trigger (as Nick has stated), you may use other approaches to force indirect DML updates on replicated tables, say