Basic template for Transactions in sqlserver

Hi,

If I simply wrap my query with:

BEGIN TRANSACTION



COMMIT TRANSACTION

If anything fails inside of that, will it automatically rollback?

From looking at other code, they seem to check for an error, if there is an error then they do a GOTO statement which then calls ROLLBACK TRANSACTION

But that seems like allot of work, to have to check for IF( @@ERROR <> 0) after every insert/update.

From stackoverflow

• yes it is important to explicitly rollback the transaction in the case that it does not work.

  I usually tell my son you only have to brush the teeth you want to keep.

  In this case, you only need to rollback the commands you don't want to execute.

• This will automatically rollback the transaction in case off error

  SET XACT_ABORT ON
  
  BEGIN TRANSACTION
  -- CODE HERE
  COMMIT TRANSACTION
  

• For transaction control you use begin, commit and rollback. You begin a transaction by supplying BEGIN TRANSACTION. Then you put the various SQL statements you need. Then you end the transaction by issuing either a commit or rollback. COMMIT TRANSACTION will commit all the changes that you did to the database after the BEGIN statement and make them permanent, so to speak. ROLLBACK TRANSACTION will rollback all changes that you did to the database after the BEGIN statement. However, it will not change variable values.

  Example:

  BEGIN TRANSACTION
  
  UPDATE table SET column = 'ABC' WHERE column = '123'
  
  COMMIT TRANSACTION
  
  --//column now has a value of 'ABC'
  
  BEGIN TRANSACTION
  
  UPDATE table SET column = 'ABC' WHERE column = '123'
  
  ROLLBACK TRANSACTION
  
  --//column still has it's previous value ('123') No changes were made.
  

  Tom Juergens : This doesn't really answer the question...

• I typically do something like this inside my stored procedures. It keeps things nice and safe and passes along any errors that I encounter.

  SET XACT_ABORT ON;
  
  BEGIN TRY
      BEGIN TRANSACTION;
  
      -- Code goes here
  
      COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
      IF @@TRANCOUNT > 0
       ROLLBACK TRANSACTION;
  
      DECLARE
       @ERROR_SEVERITY INT,
       @ERROR_STATE INT,
       @ERROR_NUMBER INT,
       @ERROR_LINE  INT,
       @ERROR_MESSAGE NVARCHAR(4000);
  
      SELECT
       @ERROR_SEVERITY = ERROR_SEVERITY(),
       @ERROR_STATE = ERROR_STATE(),
       @ERROR_NUMBER = ERROR_NUMBER(),
       @ERROR_LINE  = ERROR_LINE(),
       @ERROR_MESSAGE = ERROR_MESSAGE();
  
      RAISERROR('Msg %d, Line %d, :%s',
       @ERROR_SEVERITY,
       @ERROR_STATE,
       @ERROR_NUMBER,
       @ERROR_LINE,
       @ERROR_MESSAGE);
  END CATCH