• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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.



0
Kerau
Asked:
Kerau
  • 2
1 Solution
 
Nathan RileyFounder/CTOCommented:
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
 
Nathan RileyFounder/CTOCommented:
Yes with each individual like that, sorry forgot to put on both
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now