@@ERROR第二个例子中使用XACT_ABORT以后，第二条语句这回就无效了。

来源：互联网发布：java幂等性是什么意思编辑：程序博客网时间：2024/06/05 18:54

DECLARE @errnum AS int;
SET XACT_ABORT ON;
BEGIN TRAN;
SET IDENTITY_INSERT Production.Products ON;
-- Insert #1 will fail because of duplicate primary key
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
SET @errnum = @@ERROR;
IF @errnum <> 0
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
PRINT 'Error in first INSERT';
END;
-- Insert #2 no longer succeeds
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
SET @errnum = @@ERROR;
IF @errnum <> 0
BEGIN
-- Take actions based on the error
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
PRINT 'Error in second INSERT';
END;
SET IDENTITY_INSERT Production.Products OFF;
IF @@TRANCOUNT > 0 COMMIT TRAN;
GO

DELETE FROM Production.Products WHERE productid = 101;
PRINT 'Deleted ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';
SET XACT_ABORT OFF;
GO
SELECT XACT_STATE(), @@TRANCOUNT;
复制代码
DECLARE @errnum AS int;SET XACT_ABORT ON; BEGIN TRAN; SET IDENTITY_INSERT Production.Products ON; -- Insert #1 will fail because of duplicate primary key INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued) VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0); SET @errnum = @@ERROR; IF @errnum <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN; PRINT 'Error in first INSERT'; END; -- Insert #2 no longer succeeds INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued) VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0); SET @errnum = @@ERROR; IF @errnum <> 0 BEGIN -- Take actions based on the error IF @@TRANCOUNT > 0 ROLLBACK TRAN; PRINT 'Error in second INSERT'; END; SET IDENTITY_INSERT Production.Products OFF; IF @@TRANCOUNT > 0 COMMIT TRAN;GO DELETE FROM Production.Products WHERE productid = 101;PRINT 'Deleted ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows'; SET XACT_ABORT OFF;GOSELECT XACT_STATE(), @@TRANCOUNT;
复制代码
使用TRY/CATCH

0 0