Solved

Error in transaction -> no rollback!

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Facing problems with you memory card? Cannot access your memory card? All stored data, images, videos are lost? If these are your questions...than this small article might help you out in retrieving your lost or inaccessible data.
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…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 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

23 Experts available now in Live!

Get 1:1 Help Now