Cycle 03

Lab Cycle 3 — Set Operations, Advanced/Recursive CTEs & TCL

4 Questions
Question 04 of 04

Transaction Control Language (TCL — SAVEPOINT, ROLLBACK, COMMIT)

Problem Statement

Write an SQL transaction script to demonstrate TCL commands: 1. Begin a transaction. 2. Insert a new supplier record. 3. Create a SAVEPOINT named 'sp1'. 4. Update supplier's city. 5. Create a SAVEPOINT named 'sp2'. 6. Delete a supplier record. 7. Execute ROLLBACK TO SAVEPOINT 'sp1' to undo deletion and update. 8. Use COMMIT to permanently save transaction state.

C++ 
-- 1. Begin transaction block
BEGIN;

-- 2. Insert initial supplier
INSERT INTO Supplier (SID, Sname, branch, city, phone)
VALUES (301, 'Kavita', 'central', 'Nagpur', '9812345678');

-- 3. Create first savepoint
SAVEPOINT sp1;

-- 4. Perform an update
UPDATE Supplier 
SET city = 'Hyderabad' 
WHERE SID = 301;

-- 5. Create second savepoint
SAVEPOINT sp2;

-- 6. Delete the supplier record
DELETE FROM Supplier 
WHERE SID = 301;

-- Verify deletion within transaction (returns empty)
SELECT * FROM Supplier WHERE SID = 301;

-- 7. Rollback to sp1 (undoes delete and update, restoring state after step 2)
ROLLBACK TO SAVEPOINT sp1;

-- Verify restored state (city is 'Nagpur')
SELECT * FROM Supplier WHERE SID = 301;

-- 8. Commit the transaction to persist the insertion
COMMIT;
Question 4 of 4