Doomed transactions
You want to insert the data of two new customers into the customer table. You prepare a script controlling that if an error occurs, the transaction rollbacks and you get the message of the error. You want to control it using XACT_ABORT in combination with XACT_STATE.
This exercise is part of the course
Transactions and Error Handling in SQL Server
Exercise instructions
- Use the appropriate setting of
XACT_ABORT. - Check if there is an open transaction.
- Rollback the transaction.
- Select the error message.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Use the appropriate setting
SET XACT_ABORT ___;
BEGIN TRY
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
INSERT INTO customers VALUES ('Dylan', 'Smith', '[email protected]', '555888999');
COMMIT TRAN;
END TRY
BEGIN CATCH
-- Check if there is an open transaction
IF ___() <> 0
-- Rollback the transaction
___;
-- Select the message of the error
SELECT ___() AS Error_message;
END CATCH