In this post, we will discuss Transactions in SQL Server 2008. Also, you can check out my previous posts on:
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.
UPDATE Employees Set firstname=’AspDotNetHelp’ where ID=4
UPDATE Salary set Salary=5000 where ID=4
UPDATE Employees Set firstname=’AspDotNetHelp.com’ where ID=5
UPDATE Salary set Salary=5000 where ID=5
IF @@ERROR <> 0
Here in the 2nd example if some error occurred then it will ROLLBACK the transaction, else it will COMMIT the transaction.