sri

• Aug 19th, 2006

 

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 :USE pubsDECLARE @intErrorCode INTBEGIN 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 PROBLEMCOMMIT TRANPROBLEM:IF (@intErrorCode <> 0) BEGINPRINT 'Unexpected error occurred!' ROLLBACK TRANENDBefore 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.

Baskar

• Aug 22nd, 2006

 

Rollback is used to Return the Qurey Statement of Execution After Begin Statements

Ex :

 Begin Tran

  Select * From Employee Order by Name

  Delete From Employee

 Select * From Employee Order by Name

In This Qurey to be delete All rows from Employee Table

But ,After Execute Rollback Statement Return All rows from Employee Table

 RollBack

Roopesh Nanaware

• Nov 3rd, 2006

 

CREATE PROCEDURE [usp_delete_Pataint_master]
 (@PataintID [int],
 @BranchID [int]
 
)
AS
 BEGIN
 SET NOCOUNT ON

  BEGIN TRAN 
 
  DELETE [dbo].[Pataint_master]
  WHERE ([BranchID] = @BranchID
  AND [PataintID]  = @PataintID)

  IF @@ERROR = 0
   COMMIT TRAN
  ELSE
   ROLLBACK TRAN
  RETURN (@@ERROR)
 END

GO