Commit and Rollback in SQL

Introduction

I’ll go through SQL Server’s commit and rollback commands in this post. To guarantee the integrity of data in databases, rollback and commit are transaction statements known as Data Control Language for SQL. I describe the DCL commands Grant and Revoke.

– SQL Query Commit :-

– 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. After committing, we can’t go back.

– For modifications that last forever, commit is utilised. Any change made by a query will be permanent and obvious if Commit is used in that query. Once we commit anything, we can’t go back.

Example of a SQL Commit :-

– For a better understanding of Commit, have a look at the table below. Customer :-

Create Table :-

CREATE TABLE Employee(EmployeeId int constraint PRIMARYKEY primary key, EmployeeDepartment varchar(15));

INSERT INTO Employee (EmployeeId, EmployeeDepartment)
VALUES (‘001’, ‘CEO’);

…………………………….

……………………………n.

SELECT * FROM Employee;

 Commit Syntax  :-

COMMIT;

Example :- 

BEGIN TRAN tranemployee

UPDATE Employee
SET EmployeeDepartment = ‘ProductManager’
WHERE EmployeeId = 4

COMMIT TRAN tranemployee

– After executing Commit

– Rollback in SQL Server :-

Rollback is used to undo the changes of any command, but only before to committing. We can’t Rollback data which has been committed in the database with the using of  commit keyword.

Rollback Syntax  :-

ROLLBACK;

Example of a SQL Rollback:-

-To better understand rollback, let's look at the following table.

Create Table :-

CREATE TABLE Employee(EmployeeId int constraint PRIMARYKEY primary key, EmployeeDepartment varchar(15));

INSERT INTO Employee (EmployeeId,EmployeeDepartment)
VALUES (‘001’, ‘CEO’);
…………………

……………….N.

BEGIN TRAN Employee
UPDATE Employee
SET EmployeeDepartment = ‘ProductManager’
WHERE EmployeeId = 4

RILLBACK Trans Employee;

-After executing Rollback

Summary :-

– I discussed the SQL Server commit and rollback commands in this article. I hope you now have a better understanding of this subject. If you know anything more about this, kindly share. We appreciate your suggestions and constructive criticism.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories