SQL Server Transactions

Hello friends, Today we are going to learn SQL Server Transactions.

With the help of SQL Transaction, we can return back to previous state, if some error occurs in between the SQL code.

For example, suppose we want to update one record with in a table after update we want to insert a record in another table with in Stored Procedure,

if due to some referential or some other reason update failed, and insert succeed, our records goes into unstable state. 

It is necessary that both these queries must be complete successfully. otherwise none of execute.

we can achieve it using SQL Transaction.

 

Transactions group a set of queries into a single unit of execution. 

Each transaction begins with a particular query and ends when all the query in the unit successfully completed. 

If any of the query in between the unit fails, the transaction fails. 

So sql transaction have only two results i.e. success or failure.

Syntax:

BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ] ] [ ; ]

transaction_name: A name assigned to sql transaction.

@tran_name_variable: A user-defined variable containing a transaction name with data type char, varchar, nchar, or nvarchar assigned to sql transaction. It should be 32 characters long.

WITH MARK [ 'description' ]: It is used to marked transaction with in sql log. It should be 128 characters long. it allows us restoring a transaction log to a named mark.

BEGIN TRAN:

BEGIN TRANSACTION: Used to begins a sql transaction unit or block.

COMMIT TRANSACTION: Used to save changes into database.

ROLLBACK TRANSACTION: Used to undo every thing in database with in transaction unit.

To understand SQL Transaction lets create two tables dbo.Country and dbo.States

CREATE table dbo.Country(CountryId int Primary Key, CountryName varchar(200)) Go Create table dbo.States(StateId int primary key, CountryId int, StateName varchar(200)) Go

Lets insert data in both tables with raising some error between two queries.

INSERT INTO dbo.Country values(1,'INDIA') RAISERROR('Failed to create country',16,1) INSERT INTO dbo.States values(1,1,'PUNJAB') Go

OUTPUT: (1 row affected) Msg 50000, Level 16, State 1, Line 9 Failed to create country (1 row affected)

SELECT * FROM dbo.Country SELECT * FROM dbo.States

OUTPUT: CountryId CountryName ----------- ------------- 1 INDIA (1 row affected) StateId CountryId StateName ----------- ----------- --------- 1 1 PUNJAB (1 row affected)

Data within both the tables saved successfully even if some error raises.

Delete all data from both tables.

DELETE FROM dbo.Country Go DELETE FROM dbo.States Go

Lets apply transaction to stop inserting data if some error occurs with in unit of transaction.

BEGIN TRAN DECLARE @Error int = 0 INSERT INTO dbo.Country values(1,'INDIA') RAISERROR('Failed to create country',16,1) SET @Error = @@ERROR INSERT INTO dbo.States values(1,1,'PUNJAB') IF @Error=0 COMMIT; ELSE ROLLBACK; Go SELECT * FROM dbo.Country SELECT * FROM dbo.States

OUTPUT: CountryId CountryName ----------- ------------- (0 rows affected) StateId CountryId StateName ----------- ----------- ------------ (0 rows affected)

Not any record saves with transaction if any error occurs with in unit of transaction.

We can use transaction name with above transaction block as follows.

-- Transaction with transaction_name BEGIN TRAN TRCOUNTRY DECLARE @Error int = 0 INSERT INTO dbo.Country values(1,'INDIA') RAISERROR('Failed to create country',16,1) SET @Error = @@ERROR INSERT INTO dbo.States values(1,1,'PUNJAB') IF @Error=0 COMMIT TRAN TRCOUNTRY; ELSE ROLLBACK TRAN TRCOUNTRY; Go

We can use transaction name variable with above transaction block as follows.

-- Transaction with @tran_name_variable DECLARE @TransVar varchar(32) SET @TransVar = 'TRCOUNTRY' BEGIN TRAN @TransVar DECLARE @Error int = 0 INSERT INTO dbo.Country values(1,'INDIA') RAISERROR('Failed to create country',16,1) SET @Error = @@ERROR INSERT INTO dbo.States values(1,1,'PUNJAB') IF @Error=0 COMMIT TRAN @TransVar; ELSE ROLLBACK TRAN @TransVar; Go

If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.


Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.

 
 

{{c.Content}}

Comment By: {{c.Author}}  On:   {{c.CreatedDate|date:'dd/MM/yyyy'}} / Reply


Categories