Powered by Blogger.

Saturday, February 15, 2014

Transaction in SQL Server 2008




In this post we will discuss about Transactions in SQL Server 2008. Also you can check out my previous posts on:

- Get nth highest lowest salary in SQL Server 2008

- Transaction in SQL Server 2008

- Date validation using JavaScript

A transaction is a single operation or set of operations that succeed or fail together as a whole. Means suppose in a transaction there are 3 statements, then either it will execute all statements or it will not execute any statement.

Transactions allow you to ensure consistency in your data.

BEGIN TRAN/TRANSACTION: This will start the transaction.

COMMIT TRAN/TRANSACTION: This will save the changes.

ROLLBACK TRAN/TRANSACTION: ROLLBACK is the opposite of COMMIT. Instead of saving, it undoes all changes made in the transaction.

Difference between COMMIT and ROLLBACK is that if a severe error occurs during the execution of a transaction, SQL Server rolls back the transaction.

Example:1:

BEGIN TRAN

UPDATE Employees Set firstname='AspDotNetHelp' where ID=4

UPDATE Salary set Salary=5000 where ID=4

COMMIT TRAN

Example:2:

BEGIN TRAN

UPDATE Employees Set firstname='AspDotNetHelp.com' where ID=5

UPDATE Salary set Salary=5000 where ID=5

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END

COMMIT TRAN

Here in the 2nd example if some error occured then it will ROLLBACK the transaction, else it will COMMIT the transaction.



0 comments

Post a Comment