Transaction in Sql Server

Written By:- Isha Malhotra (Software Developer)

Email:-malhotra.isha3388@gmail.com.

Note: -if you find this article helpful then kindly leave your comment

For Asp.net Training with C# and Sql Server Click Here

Use of Transaction in Sql Server

When we required to run many statements as a single unit means either all statements run successfully or if single statement failed then all statement should be discarded in this situation, for this we use transaction.

Suppose we have two tables, first is personal_info and second is password_info. When a user insert the record in both tables, in case record inserted in personal_info and while inserting in second table there is some error then first table insetion should be cancelled. Becoz record will be entred in both table otherwise not.

This can be possible by transaction. There commands are used to manage the transaction and these are following:-

COMMIT:- commit command is used to save changes made by the transaction. This command execute when all statement runs successfully without error.

ROLLBACK:-rollback command is used to discard the transaction. This command runs when there is an error in the transaction.

SAVEPOINT:- it is a point in transaction when you can rollback the transaction back to a certain point without rolling back the entire transaction.

Example of Transaction using Commit and Rollback:-

begin transaction t1

begin try

insert into EmpSalryDetail values(9,'priya',15000,'Developer')

insert into EmpSalryDetail values(10,'priya','sneha','Developer')

commit transaction t1

select 'Transaction Executed'

end try

begin catch

rollback transaction t1

select 'Transaction Rollbacked'

end catch

in this transction while inseting in second command we did mistake. Instead of inseting int figure we are inserting varchar value which cause an error in 2^nd statement so both transaction will be rollbacked.

But if both statemt will be error free then all statement will be commited and saved in the database.