SQL Server Transaction

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
  

• 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