Правильный способ использования транзакции с несколькими вставками или обновлениями

Каков правильный способ проверки на наличие сбоев вставки/обновления и откат этой транзакции, если они есть? Я не думаю, что то, что у меня будет, будет работать, так как мои вставки/обновления - это 3 отдельных оператора, а @@ROWCOUNT будет отражать только последний выполненный оператор.

BEGIN TRANSACTION Script;
GO

INSERT INTO TableA (id) VALUES (1)
INSERT INTO TableB (id) VALUES (1)
UPDATE TableC SET id=1 WHERE id=2
GO

IF (@@ROWCOUNT=3 AND @@ERROR=0)
    BEGIN
    COMMIT
    END
ELSE
    BEGIN
    PRINT 'Error: Rolling back transaction'
    ROLLBACK TRANSACTION Script
    END
GO

Ответы

Ответ 1

Если вы положите SET XACT_ABORT ON перед началом транзакции, в случае ошибки, откат будет выдаваться автоматически.

SET XACT_ABORT ON

begin transaction

INSERT INTO TableA (id) VALUES (1)
INSERT INTO TableB (id) VALUES (1)
UPDATE TableC SET id=1 WHERE id=2

commit transaction

Если вы хотите выполнить откат самостоятельно, используйте try.. catch block.

begin transaction

begin try

  INSERT INTO TableA (id) VALUES (1)
  INSERT INTO TableB (id) VALUES (1)
  UPDATE TableC SET id=1 WHERE id=2

  commit transaction

end try

begin catch
  raiserror('Message here', 16, 1)
  rollback transaction
end catch

Ответ 2

Я не знаю, к какой версии вы подключились, но с SQL 2005 был try/catch:

begin transaction
begin try
   INSERT INTO TableA (id) VALUES (1)
   INSERT INTO TableB (id) VALUES (1)
   UPDATE TableC SET id=1 WHERE id=2
end try
begin catch
   SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
   while(@@trancount > 0)
   begin
      rollback transaction
   end
end catch
if (@@trancount <> 0)
begin
   commit transaction;
end

Ответ 3

Неудачные вставки будут выбрасываться. "Неудачные" обновления могут быть обнаружены с помощью @@ROWCOUNT.