XACT_ABORT and THROW
The wealthiest customers of the bank where you work have decided to donate the 0.01% of their current_balance to a non-profit organization. You are in charge of preparing the script to update the customer's accounts, but you have to do it only for those accounts with a current_balance with more than $5,000,000. The director of the bank tells you that if there aren't at least 10 wealthy customers, you shouldn't do this operation, because she wants to interview more customers.
You prepare a script, and of the multiple ways of doing it, you decide to use XACT_ABORT in combination with THROW. This way, if the number of affected rows is less than or equal to 10, you can throw an error so that the transaction is rolled back.
Este exercício faz parte do curso
Transactions and Error Handling in SQL Server
Instruções do exercício
- Use the appropriate setting of
XACT_ABORT. - Begin the transaction.
- If the number of affected rows is less than or equal to 10, throw the error using the
THROWstatement, with a number of 55000. - Commit the transaction if the number of affected rows is more than 10.
Exercício interativo prático
Experimente este exercício completando este código de exemplo.
-- Use the appropriate setting
SET XACT_ABORT ___;
-- Begin the transaction
___ ___;
UPDATE accounts set current_balance = current_balance - current_balance * 0.01 / 100
WHERE current_balance > 5000000;
IF @@ROWCOUNT <= 10
-- Throw the error
___ ___, 'Not enough wealthy customers!', 1;
ELSE
-- Commit the transaction
___ ___;