Commit and Rollback Commands in SQL Server

Introduction

 

In this article, I will describe Commit and Rollback commands in SQL Server. Rollback and Commit are transaction statements that are called Data Control Language for SQL and are used to ensure the integrity of data in databases. In my previous article, I describe Grant and Revoke DCL commands; for that visit, Grant and Revoke Command in SQL SERVER.

 

First of all we create a table named emp on which we enforce the Rollback and Commit commands.

 

Creation of table

 

Use the following command to the create table:

create table emp(empid int constraint PRIMARYKEY primary key, empName varchar(15))

Insertion of data

 

Use the following command for the insertion of data.

insert into emp
select 11,'d'union all
select 12,'ee'union all
select 13,'p'union all
select 14,'a'union all
select 15,'k'

Output

 

Use the following command to see the output.

select * from emp

 

Commit in SQL Server

 

Commit is used for 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

 

Here tranName 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

begin tran d
update emp set empName ='D' where empid=11
commit tran d

Here d is the name of the transactions and we update empName d to D in the table emp on the basis of empId. The change made by this command will be permanent and we could not Rollback after the commit command.

 

Output

 

 

 

Rollback in SQL Server

 

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

 

Here tranName 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 t
declare @id int;
set @id=1;
insert into emp values(@id,'d')
if(@id<10)
begin
print'An id less than 10 is not valid; query is rolled back';
rollback tran t;
end
else
begin
print 'data is inserted'
end

Here d is the name of transactions. When we provide empId less than 10 then we get.

 

Output

 

 

 

 

 

When we provide empId 16 which is greater than 10 then:

begin tran t
declare @id int;
set @id=16;
insert into emp values(@id,'d')
if(@id<10)
begin
print'Less than 10 id is not valid,query is rollbacked';
rollback tran t;
end
else
begin
print 'data is inserted'
end

Output

 

 

 

Summary

 

In this article, I described Commit and Rollback Commands in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.