We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

rollback transaction

Kerau
Kerau asked
on
Medium Priority
302 Views
Last Modified: 2012-05-06
I have written something like

BEGIN TRANSACTION

DELTE FROM A WHERE A_ID = 123

DELTE FROM B WHERE B_ID = 456

--ROLLBACK TRANSACTION
COMMIT TRANSACTION

How do i make sure that either both the trasaction happen at the same time or if error occurs none happens and the data rolls back to previous state.



Comment
Watch Question

Nathan RileyFounder

Commented:
I pulled this logic from one of my SP's.  Try it in test first
BEGIN TRANSACTION
DELTE FROM A WHERE A_ID = 123
 
DELTE FROM B WHERE B_ID = 456
 
if @@error != 0
			begin
				rollback
				return 0
			end
COMMIT TRANSACTION

Open in new window

Author

Commented:
Or should it with every delet statement

BEGIN TRANSACTION
DELTE FROM A WHERE A_ID = 123
IF @@ERROR <> 0 BEGIN
    ROLLBACK TRANSACTION
    RETURN
END
 
DELTE FROM B WHERE B_ID = 456
 
IF @@ERROR <> 0 BEGIN
    ROLLBACK TRANSACTION
    RETURN
END

COMMIT TRANSACTION
Founder
Commented:
Yes with each individual like that, sorry forgot to put on both

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.