What are the TCL commands in DBMS?

DBMSDatabaseBig Data Analytics

Transaction Control language is a language that manages transactions within the database.

It is used to execute the changes made by the DML statements.

TCL Commands

Transaction Control Language (TCL) Commands are:

  • Commit − It is used to save the transactions in the database.

  • Rollback − It is used to restore the database to that state which was last committed.

  • Begin − It is used at the beginning of a transaction.

  • Savepoint − The changes done till savpoint will be unchanged and all the transactions after savepoint will be rolled back.

Example

Given below is an example of the usage of the TCL commands in the database management system (DBMS) −

BEGIN TRANSACTION
UPDATE employees
SET empname=’bob’
WHERE empid=’001’

UPDATE employees
SET empname =’bob’
WHERE city=’hyderabad’

IF @@ROWCOUNT=5
   COMMIT TRANSACTION
ELSE
   ROLLBACK TRANSACTION

In the above example after we begin the transaction, we are trying to update the employee’s name with some value of id. If we affect five rows with our first query then, it will COMMIT transaction else It will be ROLLBACK.

Difference between Commit, rollback and savepoint of TCL commands

Sno.
Rollback
Commit
Savepoint
1.
Rollback means the database is restored to the last committed state
DML commands saves modification and it permanently saves the transaction.
Savepoint helps to save the transaction temporarily.
2.
Syntax- ROLLBACK [To SAVEPOINT_NAME];
Syntax- COMMIT;
Syntax- SAVEPOINT [savepoint_name;]
3.
Example- ROLLBACK Update5;
Example- SQL> COMMIT;
Example- SAVEPOINT table_create;
raja
Updated on 03-Jul-2021 12:24:53

Advertisements