Tcl Commands In Sql in SQL
TCL (Transaction Control Language) commands in SQL manage transactions, which are sequences of operations performed as a single logical unit of work. They help control the changes made by DML statements (INSERT, UPDATE, DELETE) ensuring data integrity.
Key TCL Commands:
| Command | Description |
|---|---|
| COMMIT | Saves all changes made in the current transaction permanently. |
| ROLLBACK | Undoes all changes made in the current transaction. |
| SAVEPOINT | Sets a point within a transaction to which you can later roll back. |
| RELEASE SAVEPOINT | Removes a previously defined savepoint. |
| SET TRANSACTION | Sets the properties for the current transaction (e.g., isolation level). |
Examples
1. COMMIT
BEGIN TRANSACTION;UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;COMMIT; -- Saves the changes permanently2. ROLLBACK
BEGIN TRANSACTION;UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;ROLLBACK; -- Undoes the changes made after transaction started3. SAVEPOINT and ROLLBACK TO SAVEPOINT
BEGIN TRANSACTION;UPDATE Orders SET Status = 'Processed' WHERE OrderID = 101;SAVEPOINT sp1;UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 102;ROLLBACK TO sp1; -- Undo changes after sp1 but keep earlier updatesCOMMIT;Why Use TCL?
Ensure atomicity: All operations in a transaction succeed or none do.
Control when changes become visible to others.
Handle errors by rolling back partial changes.
If you want, I can provide examples tailored for a specific SQL database!
