Basic template for Transactions in sqlserver

Question

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.

Answer 1

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.

Answer 2

This will automatically rollback the transaction in case off error

SET XACT_ABORT ON

BEGIN TRANSACTION
-- CODE HERE
COMMIT TRANSACTION

Answer 3

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.

Answer 4

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