title: How to handle a transaction in Sybase ASE?
body: <p>I have to insert records into a table in a test environment, so that I now know that it will throw a primary key constraint violation. And because of the scripts that will be run independantly by other people once the time to migrate from an environment to another, I wish to make my script rollback whenever it encounters a problem.</p>
  
  <p>My script is as follows:</p>
  
  <pre><code>use my_db
  go
  
  --
  -- No rows of the given "my_code" must exist, as they shall be replaced.
  --
  if exists (
          select 1
              from my_table
              where my_code like 'my_code'
  ) delete from my_table
      where my_code like 'my_code'
  
  --
  -- All rows shall be inserted altogether, or rejected altogether at once.
  --
  begin tran a
      insert into my_table (field1, field2, field3) values (value1, value2, value3)
  
      if @@error != 0 or @@transtate != 0
          begin
              rollback tran a
          end
  
      insert into my_table (field1, field2, field3) values (value1, value2, value3)
  
      if @@error != 0 or @@transtate != 0
          begin
              rollback tran a
          end
  
      commit tran a
  go
  </code></pre>
  
  <p>I have tried what I could get from these posts:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/12808846/error-handling-in-sybase"><strong>Error Handling in Sybase</strong></a></li>
  <li><a href="https://stackoverflow.com/questions/19389112/how-to-continue-executing-rest-of-while-loop-even-if-an-exception-occurs-in-syba"><strong>How to continue executing rest of while loop even if an exception occurs in sybase?</strong></a>  </li>
  <li><a href="http://database.ittoolbox.com/groups/technical-functional/sybase-l/transaction-handling-in-sybase-709992" rel="nofollow noreferrer"><strong>Transaction Handling in Sybase</strong></a></li>
  </ul>
  
  <p>I have tried with only verifying <code>@@error</code>, <code>@@transtate</code> and both, and I always get the message box reporting the error, and no records are rolled back, that is, the passing rows are still inserted.</p>
  
  <p>I wonder whether there is a way to make sure that Sybase handles the transactions adequately as expected, or else, simply make sure it doesn't autocommit rows when they are inserted as SQL Server allows it - I mean, SQL Server inherit from Sybase, after all... So was it into Sybase, or is it new to SQL Server, I don't know.</p>
  
  <p>I wish to avoid having the error and more preferably log the error and rollback or delete the inserted rows insde the transaction.</p>
  
  <p>Notice that I don't use a stored procedure. This script is a one-timer to update the database for recent changes occured in the software that uses the database.</p>