Rolling back a transaction if there is an error
On your first day of work, you were given the task of setting up transactions that record when money is transferred in your bank.
You want to prepare a simple script where $100 transfers from account_id = 1 and goes to account_id = 5. After that, it registers those movements into the transactions table. You think you have written everything correctly, but as a cautious worker, you prefer to check everything!
As a matter of fact, you did make a mistake. Instead of inserting a new transaction for account 5, you did it for account 500, which doesn't exist.
To prevent future errors, the script you create should rollback every change if an error occurs. If everything goes correctly, the transaction should be committed.
Este exercício faz parte do curso
Transactions and Error Handling in SQL Server
Instruções do exercício
- Begin the transaction.
- Correct the mistake in the operation.
- Commit the transaction if there are no errors.
- Inside the
CATCHblock, roll back the transaction.
Exercício interativo prático
Experimente este exercício completando este código de exemplo.
BEGIN TRY
-- Begin the transaction
BEGIN ___;
UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE());
UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
-- Correct it
INSERT INTO transactions VALUES (500, 100, GETDATE());
-- Commit the transaction
___ TRAN;
END TRY
BEGIN CATCH
SELECT 'Rolling back the transaction';
-- Rollback the transaction
___ ___;
END CATCH