Post by ***@peppler.org [Team Sybase]Post by Jose LuisHi,
<<snip begin>>
begin transaction1
begin transaction2
commit transaction2
rollback transaction1
<<snip begin>>
Is it posible to commit the inner transaction even though the outer
one is rolled back?
Thanks in advance,
Jose Luis.
No, I don't think that it's possible to commit the inner transaction
and rollback the outer one.
It *is* possible to rollback an inner transaction if you use
savepoints (see the "save transaction" command in the reference
manual).
Note that nested transactions do NOT require transaction names, and in
general it is better not to use them. Just keep in mind that a commit
closes the current transaction, and a rollback does a complete
rollback to the outer-most transaction.
Michael
I'll take the contrarian view. There is one way to do this,
which is to write the inner transaction in the form of a stored
procedure, and then have the outer transaction execute that
procedure as a remote procedure call to a loopback entry
in the interfaces file, using a "traditional" rpc call
(nontransactional) rather than CIS rpc handling (transactional).
declare @servernetname varchar(30)
select @servernetname=srvnetname
from master..sysservers
where srvname=@@servername
exec sp_addserver loopback, NULL, @servernetname
Then in your main transaction:
set cis rpc handling off
execute loopback.database..procname
set cis rpc handling on
The general concept is known as an "autonomous transaction";
there is an open feature request 270817 for a more straightforward
approach for them in ASE.