Solved

Error in transaction -> no rollback!

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

create procedure test1
as
   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
go

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:
1
2
3

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?
0
Comment
Question by:leon321
4 Comments
 
LVL 10

Accepted Solution

by:
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
operations).
0
 
LVL 3

Expert Comment

by:mathavra
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?.
0
 
LVL 5

Expert Comment

by:amitpagarwal
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 ?
0
 

Author Comment

by:leon321
ID: 6397291
bret,

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??

Thanks!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

805 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