[Solved] You have a transaction that inserts multiple rows into a table.... Which sequence correctly uses savepoints to achieve this? | SQL

SQL - Transactions and Data Integrity

You have a transaction that inserts multiple rows into a table. You want to undo only the last two inserts if an error occurs, but keep the earlier inserts. Which sequence correctly uses savepoints to achieve this?

ABEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp1; COMMIT;

BBEGIN; SAVEPOINT sp1; INSERT row1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp2; COMMIT;

CBEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp2; COMMIT;

DBEGIN; INSERT row1; INSERT row2; SAVEPOINT sp1; INSERT row3; ROLLBACK TO sp1; COMMIT;

Step-by-Step Solution

Solution:

Step 1: Understand the goal
To keep row1 but undo row2 and row3, place a savepoint after row1 and rollback to it.
Step 2: Identify correct sequence
BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp1; COMMIT; rolls back changes after sp1, undoing row2 and row3 while keeping row1.
Final Answer:
BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp1; COMMIT; -> Option A
Quick Check:
Rollback to savepoint before last two inserts undoes them [OK]

Quick Trick: Rollback to savepoint before last inserts to undo them [OK]

Common Mistakes:

Rolling back to wrong savepoint
Placing savepoints after all inserts
Assuming rollback undoes all inserts

Master "Transactions and Data Integrity" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Which sequence correctly uses savepoints to achieve this?

Step 1: Understand the goal

Step 2: Identify correct sequence

Final Answer:

Quick Check:

Want More Practice?