Written By:- Isha Malhotra (Software Developer)
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
insert into EmpSalryDetail values(9,'priya',15000,'Developer')
insert into EmpSalryDetail values(10,'priya','sneha','Developer')
commit transaction t1
select 'Transaction Executed'
rollback transaction t1
select 'Transaction Rollbacked'
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 2nd 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.