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 is a 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';
ENDThis exercise is part of the course
Transactions and Error Handling in SQL Server
Learn to write scripts that will catch and handle errors and control for multiple operations happening at once.
In this chapter, you will be introduced to the concept of transactions. You will discover how to commit and rollback them. You will finish by learning how to return the number of transactions and their state.
Exercise 1: TransactionsExercise 2: Transaction statementsExercise 3: Correcting a transactionExercise 4: Rolling back a transaction if there is an errorExercise 5: Choosing when to commit or rollback a transactionExercise 6: @@TRANCOUNT and savepointsExercise 7: Modifiers of the @@TRANCOUNT valueExercise 8: Checking @@TRANCOUNT in a TRY...CATCH constructExercise 9: Using savepointsExercise 10: XACT_ABORT & XACT_STATEExercise 11: XACT_ABORT behaviorExercise 12: XACT_ABORT and THROWExercise 13: Doomed transactionsWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.