Solved

Error in transaction -> no rollback!

Posted on 2001-08-15
4
843 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Moving applications to the cloud or switching services to cloud-based ones, is a stressful job.  Here's how you can make it easier.
Use of TCL script on Cisco devices:  - create file and merge it with running configuration to apply configuration changes
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now