Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Understanding NO TRANSACTION option

2 posts in Trigger Last posting was on 2008-11-06 16:18:08.0Z
Thomas Steinmaurer Posted on 2008-10-26 10:40:35.0Z
Date: Sun, 26 Oct 2008 11:40:35 +0100
From: Thomas Steinmaurer <t.steinmaurer_dontbugmewithspam_@upscene.com>
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Understanding NO TRANSACTION option
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 212.241.89.51
Message-ID: <49044792@solutions.advantagedatabase.com>
X-Trace: 26 Oct 2008 04:33:54 -0700, 212.241.89.51
Lines: 31
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!212.241.89.51
Xref: solutions.advantagedatabase.com Advantage.Trigger:413
Article PK: 1136474

Hello,

Jeremy pointed me into the direction that SQL script based triggers with
the NO TRANSACTION option are faster. And they are, by quite a factor.

But I'm not sure if I get the semantic of (not) using this option right.
Basically, what I want is:

* Client A starts a transaction
* Client A executes a DML operation => the trigger gets fired
* Client A does a commit, operations by the trigger should be committed
as well. If client A would have done a rollback, the changes by the
trigger shall be undone as well.

Am I safe when I use the NO TRANSACTION option in this case? If yes, can
someone point out a small example where omitting the NO TRANSACTION
makes sense?


Thanks!

--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com
My blog:
http://blog.upscene.com/thomas/


Mark Wilkins Posted on 2008-11-06 16:18:08.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: Advantage.Trigger
References: <49044792@solutions.advantagedatabase.com>
Subject: Re: Understanding NO TRANSACTION option
Date: Thu, 6 Nov 2008 09:18:08 -0700
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 10.24.38.228
Message-ID: <49131740@solutions.advantagedatabase.com>
X-Trace: 6 Nov 2008 09:11:44 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.Trigger:415
Article PK: 1136476

Hi Thomas,

Your understanding is correct. If the client started a transaction, then
the trigger will also be run under the context of that transaction and will
be rolled back or committed as appropriate at the end of the client's
transaction. If the trigger is run with the implicit transactions turned
on, then it is effectively run inside a nested transaction in the larger
context of the transaction started by the client (controlled via
savepoints). A rollback of the client's transaction in that case would
still rollback the trigger updates.

Mark Wilkins
Advantage R&D

"Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam_@upscene.com> wrote in
message news:49044792@solutions.advantagedatabase.com...
> Hello,
>
> Jeremy pointed me into the direction that SQL script based triggers with
> the NO TRANSACTION option are faster. And they are, by quite a factor.
>
> But I'm not sure if I get the semantic of (not) using this option right.
> Basically, what I want is:
>
> * Client A starts a transaction
> * Client A executes a DML operation => the trigger gets fired
> * Client A does a commit, operations by the trigger should be committed as
> well. If client A would have done a rollback, the changes by the trigger
> shall be undone as well.
>
> Am I safe when I use the NO TRANSACTION option in this case? If yes, can
> someone point out a small example where omitting the NO TRANSACTION makes
> sense?
>
>
> Thanks!
>
> --
> Best Regards,
> Thomas Steinmaurer
> LogManager Series - Logging/Auditing Suites supporting
> InterBase, Firebird, Advantage Database, MS SQL Server and
> NexusDB V2
> Upscene Productions
> http://www.upscene.com
> My blog:
> http://blog.upscene.com/thomas/