Transaction Rollback and Commit

Please help me to understand Rollback/Commit using the following example:

Q1. Line 20: Rollback       <-- by default it rolls back to the previous Begin Tran AddCustomer (at line 11)
                                                   (There is no need to use Rollback Tran AddCustomer, is this correct?)

I have 3 named Transactions AddCustomer (at line 11),  AddAnOrderTo_tblOrders (at line 31), and AddTitleTotblIrderDetails (at line 49)

Q2. If any of the 3 transactions fail, all transaction need to be rolled back. Where do I put my commits Tran ???.

Q3: Will Else Block with 3 commits (see line 64) do the job?

BEGIN

--SET NOCOUNT ON;

Declare @CustID int
Declare @OrderID int
Declare @AddedOrderCount int = 0
Declare @AddedCustomerCount int = 0
Declare @msg varchar(100)=''

BEGIN TRAN AddCustomer --*  *  *  *  *  *  *  *  *  *  *  *  *  T-1

	Insert Into tblCustomers(FirstName, LastName, [Address], City, [State], ZipCode, Phone, Email)
					Values(@FirstName, @LastName, @Address, @City, @State, @ZipCode, @Phone, @Email)

	-- Rollback the transaction if there were any errors
	IF @@ERROR <> 0
		BEGIN
			-- Rollback the transaction
			ROLLBACK

			-- Raise an error and return
			RAISERROR ('Error adding customer to tblCustomers.', 16, 1)
			RETURN @@ERROR
		END --*  *  *  *  *  *  *  *  *  *   *  *  *  *  *  *   *  *  *  *  * T-1
	
Set @CustID = SCOPE_IDENTITY()
Set @AddedCustomerCount = @@ROWCOUNT


BEGIN TRAN AddAnOrderTo_tblOrders -- *  *   *  *  *  *  *  *  T-2
   
	Insert Into tblOrders (CustomerID, OrderDate)
						 Values(@CustID,GETDATE())
						   
	-- Rollback the transaction if there were any errors
	IF @@ERROR <> 0
		BEGIN
			-- Rollback the transaction
			ROLLBACK AddCustomer

			-- Raise an error and return
			RAISERROR ('Adding customer with an order failed (Error occured when adding order to tblOrders).', 16, 1)
			RETURN @@ERROR
		END --*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *   *  *  *   *  *  * T-2	
			
		Set @OrderID = SCOPE_IDENTITY()

	BEGIN TRAN AddTitleTotblIrderDetails --*  *   *  T-3
		
	Insert Into tblOrderDetails(OrderID, SoftwareID, Quantity)
					   Values(@OrderID, @SoftwareID, @Quantity) 
						 
	-- Rollback the transaction if there were any errors
	IF @@ERROR <> 0
		BEGIN
			-- Rollback the transaction
			ROLLBACK AddCustomer

			-- Raise an error and return
			RAISERROR ('Adding customer with an order failed (Error occurred when adding order item to tblOrderDetails).', 16, 1)
			RETURN @@ERROR
		END --*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *   *  *  *   *  *  * T-3
	Else
	    BEGIN
		COMMIT TRAN AddCustomer -- *   *  *  *  *  *   *   *  * T-1
		COMMIT TRAN AddAnOrderTo_tblOrders -- *   *  *  *  *  * T-2
		COMMIT TRAN AddTitleTotblIrderDetails -- *   *    *   * T-3		
	    END	
End

Set @AddedOrderCount = @@ROWCOUNT

Set @msg ='Customers added: ' + CAST(@AddedCustomerCount AS NVARCHAR(10)) +
              '; Orders added: ' + CAST(@AddedOrderCount AS NVARCHAR(10));

if @@Error>0 
     Set @msg = @msg + '; SQL Server error: ' + CAST(@@Error AS NVARCHAR(10))

Set @message = @msg

Return @@Error

END 

Select allOpen in new window