TCL (Transactional Control Language) Commands.

TCL Commands:

  • TCL is abbreviation of Transactional Control Language.
  • It is used to manage different transactions occurring within a database.

 

  • COMMIT – Saves work done in transactions.
  • ROLLBACK – Restores database to original state since the last COMMIT command in transactions.
  • SAVE TRANSACTION – Sets a savepoint within a transaction.

Commit Command:

  • Commit is used for the permanent changes.
  • When we use Commit in any query then the change made by that query will be permanent and visible.
  • We can’t Rollback after the Commit.

    Syntax:
    begin tran <tranName>
    Command for operation
    commit tran <tranName>

Example:

begin tran mytran1

update student set name=’sambi-srpv’ where sno=56  // update operation

commit tran mytran1

 

Rollback Command:

 

  • Rollback is used to undo the changes made by any command but only before a commit is done.
  • We can’t Rollback data which has been committed in the database with the help of the commit keyword.

 

Syntax:                begin tran <tranName>

Command for operation
Rollback tran <tranName>

Example:

begin tran mytran1

update test set name=’ksrsrpv’ where sno=56

rollback tran mytran1

 

Save Transaction:

  • Savepoints offer a mechanism to roll back portions of transactions.
  • You create a savepoint using the SAVE TRANSACTIONsavepoint_name
  • Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

Example :  begin tran

Set of transactions

Save transaction <savepoint-name>

Set of transactions

Rollback  transaction <savepoint-name>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s