Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL TRANSACTIONS

Posted on 2007-07-27
7
Medium Priority
?
199 Views
Last Modified: 2010-03-19
Hi,

I need to add place some TSQL into a TRANSACTION.  I understand the pricipal but am confused by the ROLLBACK command.

If it did

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

Is there any point in having adding a ROLLBACK statement to this code.   What would I gain from using a ROLLBACK command?

Thanks
nutnut
0
Comment
Question by:nutnut
  • 5
  • 2
7 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 19581929
Try this

BEGIN TRAN

DECLARE @intErrorCode INT

DELETE OrderItems WHERE order_num = 12345

SELECT @intErrorCode = @@ERROR
If @intErrorCode <> 0 GoTo ErrHandle

DELETE Orders WHERE order_num = 12345

SELECT @intErrorCode = @@ERROR
If @intErrorCode <> 0 GoTo ErrHandle

COMMIT TRAN

ErrHandle:
      IF @intErrorCode <> 0
      BEGIN
            PRINT 'Unexpected error occurred!'
            ROLLBACK TRAN
      END
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19581931
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
IF  @@ERROR <> 0
BEGIN
   SELECT -201 as ErrrCode
    ROLLBACK TRANSACTION
    RETURN
END
DELETE Orders WHERE order_num = 12345
IF  @@ERROR <> 0
BEGIN
   SELECT -201 as ErrrCode
    ROLLBACK TRANSACTION
    RETURN
END
COMMIT TRANSACTION
0
 
LVL 33

Accepted Solution

by:
hongjun earned 2000 total points
ID: 19581933
Since you have more than 1 action queries, you should have a transaction so that should any one of them fail, both will be rollbacked (revert back to original state) if there's any error.

hongjun
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:hongjun
ID: 19581960
aneeshattingal,

Side track a bit.
May I ask how do you post your SQL code in "highlighted" coloring? Do you have any tools to assist you? I wish to post .NET codes highlighted.

hongjun
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19581963
I am referring to your blog :)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19582013
Actually we are not supposed to post such things here , FYI, i use Windows Live writer for Blogging.
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19582026
Thanks so much.

hongjun
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

581 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