Error in transaction -> no rollback!

Posted on 2001-08-15
Last Modified: 2010-08-05
I have the following stored procedure: (ASE 11.9 on AEX)

create procedure test1
   begin transaction
   insert into testtable1 values (1)
   insert into testtable1 values (2)
   insert into testtable1 values (2)   <-- key violation
   insert into testtable1 values (3)
   commit transaction

If I define the field as primary key, statement 3 will fail off course. But the transaction is not aborted nor rollbacked! In other words: afterwards my table will contain:

Off course, this is not what I want. Checking @@rowcount is not an option, since I also have update statements (It's possible that the statement updates 0 rows without errors)

I want my transaction to do everything succesfull OR nothing at all. Any suggestions?
Question by:leon321
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 10

Accepted Solution

bret earned 100 total points
ID: 6389522
Check @@error and/or @@transtate after each insert, and issue a rollback
if desired based on the results.  Some errors have severities such that they cancel the statement,
but not the transaction (these are indicated by @@transtate = 2 after the

Expert Comment

ID: 6390628
I am not sure but the Sybase does have a option "ignore duplicate keys" with CREATE UNIQUE INDEX option. Are you sure that you have defined the primary key constraint or created a unique index?.

Expert Comment

ID: 6391409
there is a good possibility in what matharva says.

just check if the create index definition has the ignore_dup_key attached to it ?

Author Comment

ID: 6397291

the "check @@transtate=2" comment works for me! Only drawback is that I have to check after each statement in my transaction :(

So I will accept your answer. However, since I finally have the flow in my procedures as I wanted, I get the following error when using this procedure recursively. (I call procedure test1 in procedure test1) :

"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing (previous count = 1, current count = 0)
Any thoughts??


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Building a cohesive image for your brand is vital to making an impression on consumers. When the economy is tough, brands do better than unbranded  products. This can have a huge impact on your long-term profits, as the economy goes up and down.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question