Commit and Rollback Commands in SQL Server

Introduction

            In this article I described Commit and Rollback commands in SQL Server. Rollback and Commit are transaction statements.

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

Commit:

            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 transactionName
                     Command for operation
            commit tran transactionName

            Here transactionName is the name of the transaction and the command for operation is the SQL statement that is used for the operation like making a change or inserting data etc.

Example :

Create table

create table tbl_student(Id int identity(100,1) primary key,Name varchar(200),Age int,Email Varchar(200),Address nvarchar(max))

Commit Example:

begin tran tran_student

insert into tbl_student(Name,Email,Address)values('adi','abc@gmail.com','bangalore')

commit tran tran_student

--tran_student is Transaction Name

Output

Select Commend

select * from tbl_student

Output

Rollback :

            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  transactionName
                 Command for operation
            Rollback tran transactionName

            Here transactionName is the name of the transaction and the command for the operation is the SQL statement that is used for performing operations like to make any change or insert data etc.

Example:

            We want that, if data entered by user has an empId less than 10 then the command is rolled back and a message is shown to the user "An id less than 10 is not valid; query is rolled back". 

begin tran tran_student

declare @age int;

set @age=12

insert into tbl_student(Name,Age,Email,Address)values('adi',@age,'abc@gmail.com','bangalore')

if(@age<15)

begin

print'An age less than 15 is not valid; query is rolled back';

rollback tran tran_student;

end

else

begin

print 'data is inserted'

end

            Here tran_student is the name of transactions. When we provide age less than 15 then we get

Output:

When we provide age 22 which is greater then 15 then:

begin tran tran_student

declare @age int;

set @age=22

insert into tbl_student(Name,Age,Email,Address)values('adi',@age,'abc@gmail.com','bangalore')

if(@age<15)

begin

print'An age less than 15 is not valid; query is rolled back';

rollback tran tran_student;

end

else

begin

print 'data is inserted'

end

Output: