我写了一个交易,如:
BEGIN TRAN
UPDATE [Table1]
SET [Name] = 'abcd'
WHERE [ID] = 1
UPDATE [Table2]
SET [Product] = 'efgh'
WHERE [ID] = 10
UPDATE [Table3]
SET [Customar] = 'ijkl'
WHERE [ID] = 11
现在,如果任何 UPDATE 不成功,我想回滚。例如,在表 2 中,如果没有 ID=10 的产品,则应回滚事务。怎么做?请注意,我使用的是
SQLServer 2000.
最佳答案
SQL Server 2000。如果使用 SET XACT_ABORT ON,则不需要回滚
SET XACT_ABORT ON --to ensure rollback
BEGIN TRAN -- @@TRANCOUNT + 1
UPDATE [Table1]
SET [Name] = 'abcd'
WHERE [ID] = 1
IF @@ROWCOUNT = 0 ROLLBACK TRAN
IF @@TRANCOUNT > 0
BEGIN
UPDATE [Table2]
SET [Product] = 'efgh'
WHERE [ID] = 10
IF @@ROWCOUNT = 0 ROLLBACK TRAN
END
IF @@TRANCOUNT > 0
BEGIN
UPDATE [Table3]
SET [Customar] = 'ijkl'
WHERE [ID] = 11
IF @@ROWCOUNT = 0 ROLLBACK TRAN
END
IF @@TRANCOUNT > 0 COMMIT TRAN
关于sql-server - 如果任何更新不成功如何回滚?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/4032510/