围绕多个插入或更新使用事务的正确方法 [英] Proper way to use a transaction around multiple inserts or updates

问题描述

测试插入/更新失败并回滚此事务(如果有)的正确方法是什么?我不认为我有什么会起作用，因为我的插入/更新是 3 个单独的语句，@@ROWCOUNT 只会反映最后执行的语句.

What is the proper way to test for insert/update failures and rollback this transaction if there are any? I don't think what I have will work since my inserts/updates are 3 separate statements and the @@ROWCOUNT will only reflect the last statement executed.

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

推荐答案

如果您在开始事务之前设置了 SET XACT_ABORT，如果出错，会自动回滚.

If you put SET XACT_ABORT ON before you start transaction, in case of an error, rollback will be issued automatically.

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 块.>

If you want to do rollback yourself, use 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

这篇关于围绕多个插入或更新使用事务的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！