rollback transaction

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.



KerauAsked:
Who is Participating?
 
Nathan RileyFounderCommented:
Yes with each individual like that, sorry forgot to put on both
0
 
Nathan RileyFounderCommented:
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

0
 
KerauAuthor 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.