Get startedGet started for free

Choosing when to commit or rollback a transaction

The bank where you work has decided to give $100 to those accounts with less than $5,000. However, the bank director only wants to give that money if there aren't more than 200 accounts with less than $5,000.

You prepare a script to give those $100, and of the multiple ways of doing it, you decide to open a transaction and then update every account with a balance of less than $5,000. After that, you check the number of the rows affected by the update, using the @@ROWCOUNT function. If this number is bigger than 200, you rollback the transaction. Otherwise, you commit it.

How do you prepare the script?

This exercise is part of the course

Transactions and Error Handling in SQL Server

View Course

Exercise instructions

  • Begin the transaction.
  • Check if the number of affected rows is bigger than 200.
  • Rollback the transaction if the number of affected rows is more than 200.
  • Commit the transaction if the number of affected rows is less than or equal to 200.

Hands-on interactive exercise

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

-- Begin the transaction
___ ___; 
	UPDATE accounts set current_balance = current_balance + 100
		WHERE current_balance < 5000;
	-- Check number of affected rows
	IF @@ROWCOUNT > ___ 
		BEGIN 
        	-- Rollback the transaction
			___ ___; 
			SELECT 'More accounts than expected. Rolling back'; 
		END
	ELSE
		BEGIN 
        	-- Commit the transaction
			___ ___; 
			SELECT 'Updates commited'; 
		END
Edit and Run Code