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
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