TCL Commands in SQL Server

Kiran Kumar

1y845SQL Server

What are TCL Commands in SQL Server

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul1y ago

Accepted 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 Paul1y ago
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.

+1
Jaish Mathews1y ago
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.
+1