Transactions in SQL Server

What is a Transaction?

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

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

Example:-

STEP 1:- CREATE TABLE

Create Table tblAddress

(

   AddressId int NOT NULL primary key,

   EmployeeNumber int,

   HouseNumber nvarchar(50),

   StreetAddress nvarchar(50),

   City nvarchar(10),

   PostalCode nvarchar(50)

)

Insert into tblAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')

Insert into tblAddress values (2, 102, '#15', 'Queen Street', 'Londoon', 'CR27Dk')

STEP 2:-CREATE PROCEDURE

Create Procedure USP_UpdateAddress

as

Begin

 Begin Try

  Begin Transaction

   Update tblAddress set City = 'LONDON'

   where AddressId = 1 and EmployeeNumber = 101

    Update tblAddress set City = 'LONDON'

   where AddressId = 2 and EmployeeNumber = 102

   Commit Transaction

     End Try

   Begin Catch

  Rollback Transaction

 End Catch

End