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

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

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 then 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.