ROLLBACK in SQL Server 2008

In this article I describe ROLLBACK Transactional Command in SQL Server
  • 3862

Introduction

Today we are playing around rollback and commit data control commands. Rollback command is the transactional command. These commands are used to ensure integrity within the database. First of all we create a table on which we enforce commit and rollback commands:

Rollback:

Rollback is used to undo the changes made by the query during the current transaction. If commit is used in the query than we can not rollback the changes made by query. BEGIN TRANSACTION keyword specifies, from where the transaction starts.  It is used to cancel the entire transaction from where the Begin Transaction is specified. Sometimes we delete wrong rows by mistake, in such conditions rollback is used to restores the original data.

Syntax:

begin tran transaction_name
Queries to perform
Rollback tran transaction_name

where,

transaction_name: is name assigned to the transaction, in place of transaction_name we could specify transaction variable, savepoint name, or savepoint variable.

Queries to perform: defines the list of queries whose result is to be inversed.

Example:

Suppose there is table named "Employee" having columns ID, F_Name, L_Name, Salary, City and Region. By mistake Some rows are deleted, to recover them ROLLBACK keyword is used.

Employee Table:

CreateTable.jpg

Implementation of ROLLBACK  Command

1. To rollback deleted records, type following code:

Rollback-in-sql.jpg

2. Transaction is rollback, if error occurred. Example

rollback-during-error.jpg


Categories

More Articles

© 2013 dotNetheaven. All rights reserved.