Transact SQL rollback nested trans

[agibsonsw]

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

[HansV]

The nameless transaction isn't nested properly within the named transaction.

[agibsonsw]

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.

[HansV]

I think this is OK. I prefer to indent code, it makes it easier to see what belongs to what:

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

I think you can omit the transaction name new_account from the first ROLLBACK, since new_account has already been committed by that time.

[agibsonsw]

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.

[agibsonsw]

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.

[HansV]

Yep, if you want everything to be cancelled if anything goes wrong, you need only one BEGIN TRANSACTION.