Transaction Control Language(TCL)

Transaction Control Langauage:

·        A transaction is a logical unit of work. All changes made to the database can be referred to as a transaction.

·        Transaction changes can be made permanent to a database only if they are committed.

·        This language contains Three commands.

              i) Commit

             ii) Rollback

            iii) Save point

Transaction: 

Any operation that can perform by using DML commands is known as Transaction.

Session: It can  be defined as some interval of time or some span of time. It means that the moment the user log on to the database to the user logoff to the database this span of the time is known as session. We can terminate the session two ways:

            1. Nornal Termination

            2. Abnormal Termination

1.Normal Termination:

Terminating the session by typing exit or quit SQL prompt then this termination can be called as Normal Termination. Normal Termination always saves our Transactions.

2.Abnormal Termination:

Terminating the session by directly close the window button or directly switch off the C.P.U or power off. Abnormal Termination does not save our transaction.

i)                  Commit:

This command is used for save the transactions explicitly from the moment by user logon to the database to till execute this command.

Syntax:

Commit; 

EX:

SQL>commit;

Commit complete.

ii)                Rollback:

This command is used to discard the transactions from the moment the user logon to the database till execute this command or executing the commit command too.

Syntax:

Rollback; 

Ex:

SQL>Rollback;

Rollback complete.

iii)             Save point:

·        Marks the current point in the processing of a transaction.

·        A SAVEPOINT is a marker within a transaction that allows for a partial rollback.

·        AS changes are made in a transaction, we can create SAVEPOINTS to mark different points with in the transaction.

Syntax:

Savepoint <savepoint name>;

Ex:

SQL>savepoint s1;

Savepoint created.

Note:

We can’t rollback the committed transactions but we can commit the rollback transactions