Using savepoints
Your colleague Anita needs help. She prepared a script that uses savepoints, but it doesn't work. The script marks the first savepoint, savepoint1 and then inserts the data of a customer. Then, the script marks another savepoint, savepoint2, and inserts the data of another customer again. After that, both savepoints are rolled back. Finally, the script marks another savepoint, savepoint3, and inserts the data of another customer.
Anita tells you that her script doesn't work because it has some errors, but she doesn't know how to correct them. Can you help her?
This exercise is part of the course
Transactions and Error Handling in SQL Server
Exercise instructions
- Run the code to verify there are errors.
- Correct every error.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
BEGIN TRAN;
-- Mark savepoint1
savepoint1 SAVE TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
-- Mark savepoint2
savepoint2 SAVE TRAN;
INSERT INTO customers VALUES ('Zack', 'Roberts', '[email protected]', '555919191');
-- Rollback savepoint2
savepoint2 ROLLBACK TRAN;
-- Rollback savepoint1
savepoint1 ROLLBACK TRAN;
-- Mark savepoint3
savepoint3 SAVE TRAN;
INSERT INTO customers VALUES ('Jeremy', 'Johnsson', '[email protected]', '555929292');
-- Commit the transaction
COMMIT TRAN;