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.
- 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.
