How to roll back all changes if not all statements executed successfully?

Question

How to roll back all changes if not all statements executed successfully?

nguyenanhminhxd

Valued Member

Points: 58
More actions
October 15, 2015 at 2:26 pm

#399193

I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction. Is there any examples to fix my problem?
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

nguyenanhminhxd (10/15/2015)
I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction. Is there any examples to fix my problem?
Thanks

Have you tried searching for this? It seems you know how to search the internet since you discovered transactions. Just take it one more step and you are all set.

https://www.google.com/search?q=sql+server+transaction+example&ie=utf-8&oe=utf-8

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61152 More actions · Answer 2

-- Create a table

IF OBJECT_ID('tempdb..#X') IS NOT NULL DROP TABLE #X;

CREATE TABLE #X (x int)

-- start transaction

BEGIN TRANSACTION

INSERT #X SELECT 1

INSERT #X SELECT 2

INSERT #X SELECT 'x'

-- this will only happen if all queries are successful

COMMIT WORK;

-- check to make sure nothing happened.

SELECT * FROM #X;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61152 More actions · Answer 3

Sean Lange (10/15/2015)
nguyenanhminhxd (10/15/2015)
I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction. Is there any examples to fix my problem?
Thanks
Have you tried searching for this? It seems you know how to search the internet since you discovered transactions. Just take it one more step and you are all set.
https://www.google.com/search?q=sql+server+transaction+example&ie=utf-8&oe=utf-8

I've got an even better link 😛

http://lmgtfy.com/?q=sql+server+transaction+example

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

Alan.B (10/15/2015)
Sean Lange (10/15/2015)
nguyenanhminhxd (10/15/2015)
I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction. Is there any examples to fix my problem?
Thanks
Have you tried searching for this? It seems you know how to search the internet since you discovered transactions. Just take it one more step and you are all set.
https://www.google.com/search?q=sql+server+transaction+example&ie=utf-8&oe=utf-8
I've got an even better link 😛
http://lmgtfy.com/?q=sql+server+transaction+example

LOL. I thought about that but figured I would try to be somewhat nice. I usually use the minified url if I post a link there. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Alan.B (10/15/2015)
-- Create a table IF OBJECT_ID('tempdb..#X') IS NOT NULL DROP TABLE #X; CREATE TABLE #X (x int) -- start transaction BEGIN TRANSACTION INSERT #X SELECT 1 INSERT #X SELECT 2 INSERT #X SELECT 'x' -- this will only happen if all queries are successful COMMIT WORK; -- check to make sure nothing happened. SELECT * FROM #X;

-- this will only happen if all queries are successful

No, it won't. In this particular case, with a string conversion error, it does, but that is NOT the norm, and there are all sorts of ways for an insert, update, delete to fail and not roll the transaction back. In most cases, a statement failure will not cause the transaction to roll back, unless xact_abort is on.

http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass