Hello. (SQL Server 2008 Express).
I can't easily debug the following code easily. Could someone have a quick look and see if it's okay please? Andy.
BEGIN TRANSACTION new_account
INSERT Staff (StaffID,FirstName,LastName,Salary,StartDate) VALUES (201,'Bart','Simpson',2000,'20100831');
IF EXISTS(SELECT StaffID FROM Staff WHERE StaffID=201)
BEGIN
BEGIN TRANSACTION
INSERT Holidays (StaffID,FirstDate,LastDate) VALUES (201,'20100901','20100902');
IF EXISTS (SELECT StaffID FROM Holidays WHERE (StaffID = 201) AND (FirstDate='20100901'))
COMMIT TRANSACTION new_account
ELSE
ROLLBACK TRANSACTION new_account
END
ELSE
ROLLBACK TRANSACTION
GO
Transact SQL rollback nested trans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Transact SQL rollback nested trans
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transact SQL rollback nested trans
The nameless transaction isn't nested properly within the named transaction.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Transact SQL rollback nested trans
Hello. I've revised it to the following:
BEGIN TRANSACTION new_account
INSERT Staff (StaffID,FirstName,LastName,Salary,StartDate)
VALUES (202,'Lisa','Simpson',200000,'20100831');
IF EXISTS(SELECT StaffID FROM Staff WHERE StaffID=201)
BEGIN
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT Holidays (StaffID,FirstDate,LastDate)
VALUES (202,'20100901','20100902');
IF EXISTS (SELECT StaffID FROM Holidays WHERE (StaffID = 202) AND (FirstDate='20100901'))
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION new_account
END
ELSE
ROLLBACK TRANSACTION new_account
GO
-- I think this is correct, but not sure? I think I needed two COMMITs to occur, but I only need one ROLLBACK (as it rolls-back everything)? Thanks, Andy.
BEGIN TRANSACTION new_account
INSERT Staff (StaffID,FirstName,LastName,Salary,StartDate)
VALUES (202,'Lisa','Simpson',200000,'20100831');
IF EXISTS(SELECT StaffID FROM Staff WHERE StaffID=201)
BEGIN
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT Holidays (StaffID,FirstDate,LastDate)
VALUES (202,'20100901','20100902');
IF EXISTS (SELECT StaffID FROM Holidays WHERE (StaffID = 202) AND (FirstDate='20100901'))
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION new_account
END
ELSE
ROLLBACK TRANSACTION new_account
GO
-- I think this is correct, but not sure? I think I needed two COMMITs to occur, but I only need one ROLLBACK (as it rolls-back everything)? Thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transact SQL rollback nested trans
I think this is OK. I prefer to indent code, it makes it easier to see what belongs to what:
I think you can omit the transaction name new_account from the first ROLLBACK, since new_account has already been committed by that time.
Code: Select all
BEGIN TRANSACTION new_account
INSERT Staff (StaffID,FirstName,LastName,Salary,StartDate) VALUES (202,'Lisa','Simpson',200000,'20100831');
IF EXISTS(SELECT StaffID FROM Staff WHERE StaffID=201)
BEGIN
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT Holidays (StaffID,FirstDate,LastDate) VALUES (202,'20100901','20100902');
IF EXISTS (SELECT StaffID FROM Holidays WHERE (StaffID = 202) AND (FirstDate='20100901'))
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION new_account
END
ELSE
ROLLBACK TRANSACTION new_account
GO
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Transact SQL rollback nested trans
Hello. I indent as well but it was lost in copying (and I hadn't noticed).
It works but I don't think it's quite what I want. I wanted it to be an 'all or nothing' process. Either the staff detail and holiday details are commited, or
they are all cancelled. How might I revise the code to do this please? Andy.
It works but I don't think it's quite what I want. I wanted it to be an 'all or nothing' process. Either the staff detail and holiday details are commited, or
they are all cancelled. How might I revise the code to do this please? Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Transact SQL rollback nested trans
I think the following is what I want?
BEGIN TRANSACTION new_account
INSERT Staff (StaffID,FirstName,LastName,Salary,StartDate)
VALUES (202,'Lisa','Simpson',200000,'20100831');
IF EXISTS(SELECT StaffID FROM Staff WHERE StaffID=201)
BEGIN
INSERT Holidays (StaffID,FirstDate,LastDate)
VALUES (202,'20100901','20100902');
IF EXISTS (SELECT StaffID FROM Holidays WHERE (StaffID = 202) AND (FirstDate='20100901'))
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION new_account
END
ELSE
ROLLBACK TRANSACTION new_account
GO
-- I don't think I need to nest any transactions? Andy.
BEGIN TRANSACTION new_account
INSERT Staff (StaffID,FirstName,LastName,Salary,StartDate)
VALUES (202,'Lisa','Simpson',200000,'20100831');
IF EXISTS(SELECT StaffID FROM Staff WHERE StaffID=201)
BEGIN
INSERT Holidays (StaffID,FirstDate,LastDate)
VALUES (202,'20100901','20100902');
IF EXISTS (SELECT StaffID FROM Holidays WHERE (StaffID = 202) AND (FirstDate='20100901'))
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION new_account
END
ELSE
ROLLBACK TRANSACTION new_account
GO
-- I don't think I need to nest any transactions? Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transact SQL rollback nested trans
Yep, if you want everything to be cancelled if anything goes wrong, you need only one BEGIN TRANSACTION.
Best wishes,
Hans
Hans