Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Tim_Green at mercer dot com
Subject: RE: Strange transaction behavior
Date: May 20 1998 6:43PM

> It turned out that this was the case.  There were triggers on 
> some user
> tables that were causing some updates to fail, and thus force the
> transaction to be rolled back (I don't know if *all* the 
> updates failed;
> with my test data most of the updates end up updating 0 
> columns anyway).
> However...(this is probably more a Sybase question, but...) I 
> don't want
> to rollback the transaction on failure of the ad-hoc updates; only if
> the stored procedure updates fail.  Is there anyway I can tell the SQL
> server to *not* rollback the transaction automatically on an error?

It depends on the source of the error.  If the errors your talking about are
coming from user-defined errors detected in the triggers, then yes.  First,
since you don't care if the updates succeed or not, why not change your code to
start the transaction after the adhoc queries but before the stored procedure
        begin tran
        exec sp...
        exec sp...
        commit tran

If you want the updates to also be rolled back if the stored procedures fail
then you'll need to change the trigger code.  Often triggers are coded to issue
a 'rollback transaction' when they detect an error, which rolls back to the
outer most transaction.  In Sybase 10 or later the trigger can use the rollback
trigger command which rolls back the trigger and changes made by the SQL
statement that triggered it, but any transactions open when the SQL was executed
remain unaffected.  If you're not in a position where you can change the
triggers, then you're out of luck.

If the errors causing your script to abort are detected by Sybase, then again,
for the most part you're out of luck.  Any errors with a severity greater than
16 cause Sybase to rollback the transaction and abort the currently executing
batch.  Most errors fall into this category, with the exception of permission
errors, rule violations and duplicate key errors.  These, if memory serves,
abort the SQL statement, but not the batch or the transaction (unless the error
occurs IN a trigger).

I highly recommend reading the section on transactions in the Sybase Reference
Manual - Volume 1.  It explains in detail what happens to a SQL batch, a
transaction and the SQL statement when certain errors occur, including
differences in the behavior when the error is detected inside or outside of a

Good luck,
Tim Green                                 
Administrative Solutions Group
An ADP/Mercer Alliance