Binaryworld - How to implement simple transaction in SQL Server ... [ SQL Server -> Locks-Transactions ]

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Transact-SQL statements into a single transaction using the following statements:

Begin Transaction
Rollback Transaction
Commit Transaction
If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.

Here is an example of a transaction :

Click here to copy the following block

USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN
  UPDATE Authors
  SET Phone = '415 354-9866'
  WHERE au_id = '724-80-9391'

  SELECT @intErrorCode = @@ERROR
  IF (@intErrorCode <> 0) GOTO PROBLEM

  UPDATE Publishers
  SET city = 'Calcutta', country = 'India'
  WHERE pub_id = '9999'

  SELECT @intErrorCode = @@ERROR
  IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
  ROLLBACK TRAN
END

Before the real processing starts, the BEGIN TRAN statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two UPDATE statements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the COMMIT TRAN statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by if statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing. Note: Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK.