SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Transaction COMMIT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 10/09/2013 :  06:09:34  Show Profile  Reply with Quote
Hi

I have this query:

BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('John C', 'Chicago', 'IL')
COMMIT TRANSACTION

BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('Bubba C', 'Austin', 'TX')
ROLLBACK TRANSACTION

SELECT * FROM CUSTOMER

Now when I execute the first query, it addds John to my table as it's suppose to, and when I execute the second query it doesn't add Bubba as it's suppose to, but it also delete John who's added in the first query.

Why is it so?

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  06:59:25  Show Profile  Reply with Quote
Nope its working as its upposed to when i tried. I tried running both together as well as separate. In both the cases it deleted only Bubba and Johns record remains in table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 10/09/2013 :  08:06:06  Show Profile  Reply with Quote
Hi

The whole query has nest,

EGIN TRANSACTION [new_Account]
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES ('Izetta','Greenville', 'AL')

IF EXISTS (SELECT * FROM CUSTOMER WHERE NAME = 'Izetta')
BEGIN
BEGIN TRANSACTION
INSERT BALANCES(AVERAGE_BAL, CURRENT_BAL)
VALUES(1250.76, 1431.26)
END
ELSE ROLLBACK TRANSACTION
COMMIT

BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('John C', 'Chicago', 'IL')
COMMIT TRANSACTION

BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('Bubba C', 'Austin', 'TX')
ROLLBACK TRANSACTION

select * from Customer

Then when I execute the last query then it get rid of everything else before it.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 10/09/2013 :  08:40:40  Show Profile  Reply with Quote
Nested transactions behave in a "funny" way in SQL Server. The outermost commit is what will stick. And, any rollback will roll it back. Take a look at this page - they explain it much better than I can, with examples and all: http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx

The behavior you are seeing is expected behavior If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000