Checking @@TRANCOUNT in a TRY...CATCH construct

The owner of account 10 has won a raffle and will be awarded $200. You prepare a simple script to add those $200 to the current_balance of account 10. You think you have written everything correctly, but you prefer to check your code.

In fact, you made a silly mistake when adding the money: SET current_balance = 'current_balance' + 200. You wrote 'current_balance' as a string, which generates an error.

The script you create should rollback every change if an error occurs, checking if there is an open transaction. If everything goes correctly, the transaction should be committed, also checking if there is an open transaction.

This exercise is part of the course

Transactions and Error Handling in SQL Server

View Course

Exercise instructions

Begin the transaction.
Correct the mistake in the operation.
Inside the TRY block, check if there is a transaction and commit it.
Inside the CATCH block, check if there is a transaction and roll it back.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

BEGIN TRY
	-- Begin the transaction
	___ ___;
    	-- Correct the mistake
		UPDATE accounts SET current_balance = 'current_balance' + 200
			WHERE account_id = 10;
    	-- Check if there is a transaction
		IF ___ > 0     
    		-- Commit the transaction
			___ ___;
     
	SELECT * FROM accounts
    	WHERE account_id = 10;      
END TRY
BEGIN CATCH  
    SELECT 'Rolling back the transaction'; 
    -- Check if there is a transaction
    IF ___ > 0   	
    	-- Rollback the transaction
        ___ ___;
END CATCH

Edit and Run Code