--Transactions extend batches
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Sales.SalesOrderHeader... --Succeeds
INSERT INTO Sales.SalesOrderDetail... --Fails
COMMIT TRANSACTION -- If no errors, transaction completes
END TRY
BEGIN CATCH
--Inserted rows still exist in Sales.SalesOrderHeader SELECT ERROR_NUMBER()
ROLLBACK TRANSACTION --Any transaction work undone
END CATCH;
复制代码
复制代码
BEGIN TRY
BEGIN TRAN;
SET IDENTITY_INSERT Production.Products ON;
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
VALUES(101, N'Test2: Bad categoryid', 1, 10, 18.00, 0);
SET IDENTITY_INSERT Production.Products OFF;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627 -- Duplicate key violation
BEGIN
PRINT 'Primary Key violation';
END
ELSE IF ERROR_NUMBER() = 547 -- Constraint violations
BEGIN
PRINT 'Constraint violation';
END
ELSE
BEGIN
PRINT 'Unhandled error';
END;
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH;
复制代码
复制代码
-- revise the CATCH block using variables to capture error information and re-raise the error using RAISERROR.
USE TSQL2012;
GO
SET NOCOUNT ON;
DECLARE @error_number AS INT, @error_message AS NVARCHAR(1000), @error_severity AS INT;
BEGIN TRY
BEGIN TRAN;
SET IDENTITY_INSERT Production.Products ON;
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
VALUES(101, N'Test2: Bad categoryid', 1, 10, 18.00, 0);
SET IDENTITY_INSERT Production.Products OFF;
COMMIT TRAN;
END TRY
BEGIN CATCH
SELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT';
SELECT @error_number = ERROR_NUMBER(), @error_message = ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY();
RAISERROR (@error_message, @error_severity, 1);
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH;
复制代码
复制代码
-- use a THROW statement without parameters re-raise (re-throw) the original error message and send it back to the client.
USE TSQL2012;
GO
BEGIN TRY
BEGIN TRAN;
SET IDENTITY_INSERT Production.Products ON;
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
VALUES(101, N'Test2: Bad categoryid', 1, 10, 18.00, 0);
SET IDENTITY_INSERT Production.Products OFF;
COMMIT TRAN;
END TRY
BEGIN CATCH
SELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT';
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
GO
SELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT';
复制代码
--Transactions extend batchesBEGIN TRY BEGIN TRANSACTION INSERT INTO Sales.SalesOrderHeader... --Succeeds INSERT INTO Sales.SalesOrderDetail... --Fails COMMIT TRANSACTION -- If no errors, transaction completesEND TRYBEGIN CATCH --Inserted rows still exist in Sales.SalesOrderHeader SELECT ERROR_NUMBER() ROLLBACK TRANSACTION --Any transaction work undoneEND CATCH;
复制代码
复制代码
BEGIN TRYBEGIN TRAN; SET IDENTITY_INSERT Production.Products ON; INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued) VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0); INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued) VALUES(101, N'Test2: Bad categoryid', 1, 10, 18.00, 0); SET IDENTITY_INSERT Production.Products OFF;COMMIT TRAN;END TRYBEGIN CATCH IF ERROR_NUMBER() = 2627 -- Duplicate key violation BEGIN PRINT 'Primary Key violation'; END ELSE IF ERROR_NUMBER() = 547 -- Constraint violations BEGIN PRINT 'Constraint violation'; END ELSE BEGIN PRINT 'Unhandled error'; END; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;END CATCH;
复制代码
复制代码
-- revise the CATCH block using variables to capture error information and re-raise the error using RAISERROR. USE TSQL2012;GOSET NOCOUNT ON;DECLARE @error_number AS INT, @error_message AS NVARCHAR(1000), @error_severity AS INT;BEGIN TRYBEGIN TRAN; SET IDENTITY_INSERT Production.Products ON; INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued) VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0); INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued) VALUES(101, N'Test2: Bad categoryid', 1, 10, 18.00, 0); SET IDENTITY_INSERT Production.Products OFF; COMMIT TRAN;END TRYBEGIN CATCH SELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT'; SELECT @error_number = ERROR_NUMBER(), @error_message = ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(); RAISERROR (@error_message, @error_severity, 1); IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;END CATCH;
复制代码
复制代码
-- use a THROW statement without parameters re-raise (re-throw) the original error message and send it back to the client. USE TSQL2012;GOBEGIN TRYBEGIN TRAN; SET IDENTITY_INSERT Production.Products ON; INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued) VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0); INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued) VALUES(101, N'Test2: Bad categoryid', 1, 10, 18.00, 0); SET IDENTITY_INSERT Production.Products OFF;COMMIT TRAN;END TRYBEGIN CATCH SELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT'; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; THROW;END CATCH;GOSELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT';
复制代码