TRANSACTION

• Rakesh_24

  Transaction Savepoints

  New Information - SQL Server 2000 SP3.

  Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement, and then later execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of a transaction.

  Savepoints are useful in situations where errors are unlikely to occur. The use of a savepoint to roll back part of a transaction in the case of an infrequent error can be more efficient than having each transaction test to see if an update is valid before making the update. Updates and rollbacks are expensive operations, so savepoints are effective only if the probability of encountering the error is low and the cost of checking the validity of an update beforehand is relatively high.

  This example shows the use of a savepoint in an order system in which there is a low probability of running out of stock because the company has effective suppliers and reorder points. Usually an application would verify that there is enough stock on hand before attempting to make the updates that would record the order. This example assumes that, for some reason (such as connecting over a slow modem or WAN), first verifying the quantity of stock available is relatively expensive. The application could be coded to just make the update, and if it gets an error indicating that there is not enough stock, it rolls back the update. In this case, a quick check of @@ERROR after the insert is much faster than verifying the amount before the update.

  The InvCtrl table has a CHECK constraint that triggers a 547 error if the QtyInStk column goes below 0. The OrderStock procedure creates a savepoint. If a 547 error occurs, it rolls back to the savepoint and returns the number of items on hand to the calling process. The calling process can then decide if it wants to replace the order for the quantity on hand. If OrderStock returns a 0, the calling process knows there was enough stock on hand to satisfy the order.

  Security Note Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input. For more information, see Validating User Input.

  SET NOCOUNT OFF
  GO
  USE pubs
  GO
  CREATE TABLE InvCtrl
     (WhrhousID   int,
     PartNmbr   int,
     QtyInStk   int,
     ReordrPt   int,
     CONSTRAINT InvPK PRIMARY KEY
     (WhrhousID, PartNmbr),
     CONSTRAINT QtyStkCheck CHECK (QtyInStk > 0) )
  GO
  CREATE PROCEDURE OrderStock @WhrhousID int, @PartNmbr int,
        @OrderQty int
  AS
  DECLARE @ErrorVar int
  SAVE TRANSACTION StkOrdTrn
  UPDATE InvCtrl SET QtyInStk = QtyInStk - @OrderQty
  WHERE WhrhousID = 1
    AND PartNmbr = 1
  SELECT @ErrorVar = @@error
  IF (@ErrorVar = 547)
  BEGIN
    ROLLBACK TRANSACTION StkOrdTrn
    RETURN (SELECT QtyInStk
       FROM InvCtrl
       WHERE WhrhousID = @WhrhousID
        AND PartNmbr = @PartNmbr)
  END
  ELSE
    RETURN 0
  GO
  

  
  
  

• pieter.vp

  maybe this is the one you need

  @@ERROR

  Returns the error number for the last Transact-SQL statement executed.

  Syntax

  @@ERROR

  Return Types

  integer

  Remarks

  When MicrosoftR SQL Server completes the execution of a Transact-SQL statement, @@ERROR is set to 0 if the statement executed successfully. If an error occurs, an error message is returned. @@ERROR returns the number of the error message until another Transact-SQL statement is executed. You can view the text associated with an @@ERROR error number in the sysmessages system table.

  Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.

  Examples

  A. Use @@ERROR to detect a specific error

  This example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.

  USE pubs
  GO
  UPDATE authors SET au_id = '172 32 1176'
  WHERE au_id = "172-32-1176"
  
  IF @@ERROR = 547
    print "A check constraint violation occurred"
  

  B. Use @@ERROR to conditionally exit a procedure

  The IF...ELSE statements in this example test @@ERROR after an INSERT statement in a stored procedure. The value of the @@ERROR variable determines the return code sent to the calling program, indicating success or failure of the procedure.

  USE pubs
  GO
  
  -- Create the procedure.
  CREATE PROCEDURE add_author 
  @au_id varchar(11),@au_lname varchar(40),
  @au_fname varchar(20),@phone char(12),
  @address varchar(40) = NULL,@city varchar(20) = NULL,
  @state char(2) = NULL,@zip char(5) = NULL,
  @contract bit = NULL
  AS
  
  -- Execute the INSERT statement.
  INSERT INTO authors
  (au_id, au_lname, au_fname, phone, address, 
   city, state, zip, contract) values
  (@au_id,@au_lname,@au_fname,@phone,@address,
   @city,@state,@zip,@contract)
  
  -- Test the error value.
  IF @@ERROR <> 0 
  BEGIN
    -- Return 99 to the calling program to indicate failure.
    PRINT "An error occurred loading the new author information"
    RETURN(99)
  END
  ELSE
  BEGIN
    -- Return 0 to the calling program to indicate success.
    PRINT "The new author information has been loaded"
    RETURN(0)
  END
  GO
  

  C. Use @@ERROR to check the success of several statements

  This example depends on the successful operation of the INSERT and DELETE statements. Local variables are set to the value of @@ERROR after both statements and are used in a shared error-handling routine for the operation.

  USE pubs
  GO
  DECLARE @del_error int, @ins_error int
  -- Start a transaction.
  BEGIN TRAN
  
  -- Execute the DELETE statement.
  DELETE authors
  WHERE au_id = '409-56-7088'
  
  -- Set a variable to the error value for 
  -- the DELETE statement.
  SELECT @del_error = @@ERROR
  
  -- Execute the INSERT statement.
  INSERT authors
    VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
    '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
  -- Set a variable to the error value for 
  -- the INSERT statement.
  SELECT @ins_error = @@ERROR
  
  -- Test the error values.
  IF @del_error = 0 AND @ins_error = 0
  BEGIN
    -- Success. Commit the transaction.
    PRINT "The author information has been replaced"  
    COMMIT TRAN
  END
  ELSE
  BEGIN
    -- An error occurred. Indicate which operation(s) failed
    -- and roll back the transaction.
    IF @del_error <> 0 
     PRINT "An error occurred during execution of the DELETE 
     statement." 
  
    IF @ins_error <> 0
     PRINT "An error occurred during execution of the INSERT 
     statement." 
  
    ROLLBACK TRAN
  END
  GO
  

  D. Use @@ERROR with @@ROWCOUNT

  This example uses @@ERROR with @@ROWCOUNT to validate the operation of an UPDATE statement. The value of @@ERROR is checked for any indication of an error, and @@ROWCOUNT is used to ensure that the update was successfully applied to a row in the table.

  USE pubs
  GO
  CREATE PROCEDURE change_publisher
  @title_id tid, 
  @new_pub_id char(4) 
  AS
  
  -- Declare variables used in error checking.
  DECLARE @error_var int, @rowcount_var int
  
  -- Execute the UPDATE statement.
  UPDATE titles SET pub_id = @new_pub_id 
  WHERE title_id = @title_id 
  
  -- Save the @@ERROR and @@ROWCOUNT values in local 
  -- variables before they are cleared.
  SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
  
  -- Check for errors. If an invalid @new_pub_id was specified
  -- the UPDATE statement returns a foreign-key violation error #547.
  IF @error_var <> 0
  BEGIN
    IF @error_var = 547
    BEGIN
     PRINT "ERROR: Invalid ID specified for new publisher"
     RETURN(1)
    END
    ELSE
    BEGIN
     PRINT "ERROR: Unhandled error occurred"
     RETURN(2)
    END
  END
  
  -- Check the rowcount. @rowcount_var is set to 0 
  -- if an invalid @title_id was specified.
  IF @rowcount_var = 0 
  BEGIN
    PRINT "Warning: The title_id specified is not valid"
    RETURN(1)
  END
  ELSE
  BEGIN
    PRINT "The book has been updated with the new publisher"
    RETURN(0)
  END
  GO
  

  
  
  

• GrizzlyAdams

  Well, not quite.

  Transaction keywords aren't used for errorhandling purposes per se. You can view it more like a concept of 'packaging'. You use transactions when you have more than one operation (or 'step') that should work together as one logical unit. Like the example, it's been decided that if there is an insert into titles, then there must also be a succesful insert into titleauthor. If both steps succeeds, then the transaction is committed, if one fails, then it must look like no work has been done. (hence the rollback)

  The errorhandling after each insert is there to make sure that the 'transaction flow' will behave as intended.
  But there is a difference between 'transaction handling' and 'error handling'.

  /Kenneth

  
  

• Gabe19

  a "Transaction" in SQL server is very different from bussiness transactions.

  A sql server transaction is composed of a job or a collection of job that must be taken as a unit. each piece of job is known as token. for a transaction to commit all tokens must commit otherwise the entire transaction fails.

  an example of a transaction is a fund transfer in which two jobs are carried on as a unit.

  the first token might be. "decrease the amount to be transfered from account A balance" and the second token is "increase the balance of acount B" all of this tokens must commit otherwise an inconsistency will occur.

  A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a savepoint, it must proceed to completion (with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement), or it must be canceled altogether (by rolling the transaction back to its beginning). To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone.

   

  
  
  

• Cyber

  Errorhandling and transactionhandling are two different concepts, that sometimes are used together. But they are used for different things.

  The common factor is that you need to think carefully how to design transactions, so that they work as expected. With this in mind, you then also need to think carefully how to do errorhandling, so that errors are handled in a way that is satisfactory.

  These two subjects are very large and there is very much written and said about them.
  The best way to start is to look in BOL and start reading about errorhandling and transaction handling and design. It's well worth the effort, because both are essential to understand when writing robust T-SQL code.

  =;o)
  /Kenneth

  
  

• softwater

  Thanks for your explanatory reply and I have still some doubts.
  When an error occured in this scope(TRANSACTION SCOPE), this does not make the program finished(aborted), and then after the error occored we go to the if statement snd look for the error and after we make some messages print on the monitor.
  So, I ask that if TRANSACTION SCOPE did not allowed to handle(manage) the error, could be really possible to go to the next step(if statement)
  
  Thanks
  

  
  
  
• TRANSACTION