SQL SERVER Topic

Transactions in SQL Server
What is a Transaction?

The Transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.
OR

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.

Transaction modes in SQL Server:-
  1. Autocommit transactions:- Each individual statement is a transaction.
  2. Explicit transactions:- Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
  3. Implicit transactions:- A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
  4. Batch-scoped transactions:- Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.


Transaction processing follows these steps:-
  1. Begin a transaction
  2. Process database commands
  3. Check for errors
    If errors occurred,
    rollback the transaction,
    else,
    commit the transaction
Now, let understands by example:- We are using SQL SERVER, so first create table named is tblEmployee and insert some records as shown in below. 

  

CREATE TABLE tblEmployee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 EmailId varchar(30),
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)


----------------Insert some records into---->>tblEmployee-----------------------

INSERT INTO tblEmployee VALUES(1001,'Santosh kumar singh','s@gmail.com',1200,'A-566')
INSERT INTO tblEmployee VALUES(1002,'Reena kumari','r@gmail.com',1700,'H-560')
INSERT INTO tblEmployee VALUES(1003,'Pooaj','p@gmail.com',1200,'P-566')
INSERT INTO tblEmployee VALUES(1004,'Prem kumar','pk@gmail.com',1900,'D-320')
INSERT INTO tblEmployee VALUES(1005,'Jon','j@gmail.com',1300,'J-506')
INSERT INTO tblEmployee VALUES(1006,'Jemmy','jm@gmail.com',1100,'K-500')
INSERT INTO tblEmployee VALUES(1007,'Santosh kumar','sk@gmail.com',1800,'T-120')

SELECT * FROM tblEmployee




Now, we want to update the record of employee where EmpID=1002. If the UPDATE statements succeed, without any errors, then the transaction is committed. If there are errors, then the control is immediately transferred to the catch block. The ROLLBACK TRANSACTION statement, in the CATCH block, rolls back the transaction, and any data that was written to the database by the commands is backed out. For this we create a Store Procedure as shown in below. 



  

CREATE PROC USP_UpdatetblEmployee
 @EmpID int,
 @EmpName varchar (50),
 @EmailId varchar(30),
 @Salary int,
 @Address varchar (200)
 AS
 BEGIN
   Begin Try
      Begin TRAN -----use TRAN or Transaction
       UPDATE tblEmployee SET EmpName=@EmpName,EmailId=@EmailId,Salary=@Salary,Address=@Address WHERE EmpID=@EmpID
       PRINT 'RECORD HAS BEEN UPDATED.'
      COMMIT
   End Try
   
   Begin Catch
   PRINT 'RECORD HAS NOT BEEN UPDATED.'
   ROLLBACK TRAN
   End Catch
 
 END

 --------------------update the following details---------------------

 USP_UpdatetblEmployee 1002,'Reena kumari gupta','rk@gmail.com',1600,'A-4783'