Art Of Programming

musings by Dmytrii Nagirniak

Automatically Rollback Transaction on Error in T-SQL

During any kind of data migration you don’t want to leave any data changed in the middle of migration when an error occurs.
By default Microsoft Sql Server continues batch execution if an error occurs. It skips ONLY the statement with the error.
To make sure it will rollback the transaction use SET XACT_ABORT ON before opening a transaction.
Just some stupid example from the top of my head:
SET XACT_ABORT ON

BEGIN TRAN

  UPDATE PaymentTax
    SET Amount = p.Amount * 0.1
  FROM
    Payment p, PaymentTax t
  WHERE
    t.PaymentId = p.Id
    AND p.PaymentType = 1 -- CreditCard

  -- Do more stuff in-between

  UPDATE Payment
    SET Amount = Amount - Amount*0.1
  WHERE PaymentType=1 -- CreditCard

COMMIT TRAN

setting the SET XACT_ABORT ON will ensure there will be no possibility of changing Payments’ amount without changing tax.
The XACT_ABORT is available at least since Sql Server 20000 so should be ok if you work with different versions of SqlServer.

Comments