TCL Commands in SQL Server

Question

What are TCL Commands in SQL Server

Tuhin Paul · Answer

Part -2 Say you are transferring funds from one account to another. If any step fails the entire transaction should be rolled back to maintain data integrity. -- Start a transaction BEGIN TRANSACTION; -- Deduct amount from Account A UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1; -- Add amount to Account B UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2; -- Check for errors IF @@ERROR 0 BEGIN -- Rollback the transaction if there's an error ROLLBACK; PRINT 'Transaction rolled back due to an error.'; END ELSE BEGIN -- Commit the transaction if there are no errors COMMIT; PRINT 'Transaction committed successfully.'; END When to use : When performing multiple related operations that must succeed or fail together. To roll back changes if an error occurs during execution. To check the database remains consistent during complex operations. -- Start a transaction BEGIN TRANSACTION; -- Insert a new record INSERT INTO Employees (EmployeeID Name Salary) VALUES (101 'John Doe' 50000); -- Create a savepoint SAVE TRANSACTION AfterInsert; -- Update the salary UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 101; -- Rollback to the savepoint ROLLBACK TRANSACTION AfterInsert; -- Commit the transaction COMMIT;

Tuhin Paul · Answer

Part -1 Transactions are sequences of operations performed as a single logical unit of work. TCL commands checks data integrity by allowing you to control when changes are saved permanently or rolled back in case of errors.

Jaish Mathews · Answer

TCL (Transaction Control Language) commands in SQL Server are used to manage transactions within the database. These commands allow you to ensure data integrity and control over how changes are committed or reverted in the database. The primary TCL commands are: 1. COMMIT Purpose : Saves all changes made during the current transaction to the database. Syntax : COMMIT; Usage : Finalize a transaction and make all its changes permanent. Cannot be undone once issued. 2. ROLLBACK Purpose : Reverts all changes made during the current transaction. Syntax : ROLLBACK; Usage : Undo changes made in a transaction if something goes wrong. Can be used after explicitly starting a transaction using BEGIN TRANSACTION . 3. SAVEPOINT Purpose : Creates a temporary savepoint within a transaction allowing partial rollbacks to a specific point. Syntax : SAVE TRANSACTION savepoint_name; Usage : Define checkpoints in a transaction to partially revert changes up to that point. Useful for complex transactions where only certain parts need to be undone. 4. SET TRANSACTION Purpose : Sets the properties for the current transaction like isolation levels. Syntax : SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }; Usage : Control how transactions interact with one another regarding data visibility and locking. Example Usage BEGIN TRANSACTION; -- Insert data INSERT INTO Employees (Name Position) VALUES ('John Doe' 'Developer'); -- Savepoint SAVE TRANSACTION SavePoint1; -- Update data UPDATE Employees SET Position = 'Senior Developer' WHERE Name = 'John Doe'; -- Rollback to the savepoint ROLLBACK TRANSACTION SavePoint1; -- Commit the transaction COMMIT; Key Points Transactions help maintain database consistency by grouping operations. Use COMMIT to save changes and ROLLBACK to undo changes. SAVEPOINT adds flexibility in managing complex transactions. Always test transactions thoroughly to ensure data integrity.