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

最新推荐文章于 2025-02-22 10:53:31 发布

原创最新推荐文章于 2025-02-22 10:53:31 发布 · 504 阅读

CC 4.0 BY-SA版权

本文介绍了一种在SQL中处理事务及错误的方法，通过设置XACT_ABORT选项来确保数据一致性，并利用@@ERROR来捕获并处理插入操作时可能出现的问题。

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