测试插入/更新失败的正确方法是什么？我不认为我的工作是什么,因为我的插入/更新是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
解决方法
如果在开始事务之前将SET XACT_ABORT设置为ON,则为
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
如果你想自己做回滚,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
