在 MS SQL Server Management Studio 中处理事务的最佳方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/506602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to work with transactions in MS SQL Server Management Studio
提问by Niels Bosma
Let's say I have an SQL statement that's syntactically and semantically correct so it executes.
假设我有一个语法和语义正确的 SQL 语句，因此它可以执行。
In Management Studio (or any other query tool) how can I test SQL statements, and if I notice that they broke something, rollback (in a separate query?)
在 Management Studio（或任何其他查询工具）中，我如何测试 SQL 语句，如果我注意到它们破坏了某些内容，请回滚（在单独的查询中？）
回答by John Sansom
The easisest thing to do is to wrap your code in a transaction, and then execute each batch of T-SQL code line by line.
最简单的做法是将您的代码包装在一个事务中，然后逐行执行每批 T-SQL 代码。
For example,
例如，
Begin Transaction
-Do some T-SQL queries here.
Rollback transaction -- OR commit transaction
If you want to incorporate error handling you can do so by using a TRY...CATCH BLOCK. Should an error occur you can then rollback the tranasction within the catch block.
如果您想合并错误处理，您可以使用 TRY...CATCH BLOCK 来实现。如果发生错误，您可以回滚 catch 块中的事务。
For example:
例如：
USE AdventureWorks;
GO
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
See the following link for more details.
有关更多详细信息，请参阅以下链接。
http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://msdn.microsoft.com/en-us/library/ms175976.aspx
Hope this helps but please let me know if you need more details.
希望这会有所帮助，但如果您需要更多详细信息，请告诉我。
回答by HLGEM
I want to add a point that you can also (and should if what you are writing is complex) add a test variable to rollback if you are in test mode. Then you can execute the whole thing at once. Often I also add code to see the before and after results of various operations especially if it is a complex script.
我想补充一点，如果您处于测试模式，您也可以（并且如果您正在编写的内容很复杂，则应该）添加一个测试变量以进行回滚。然后你可以一次执行整个事情。通常我还会添加代码来查看各种操作的前后结果，特别是如果它是一个复杂的脚本。
Example below:
下面的例子：
USE AdventureWorks;
GO
DECLARE @TEST INT = 1--1 is test mode, use zero when you are ready to execute
BEGIN TRANSACTION;
BEGIN TRY
IF @TEST= 1
BEGIN
SELECT *FROM Production.Product
WHERE ProductID = 980;
END
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
IF @TEST= 1
BEGIN
SELECT *FROM Production.Product
WHERE ProductID = 980;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0 AND @TEST = 0
COMMIT TRANSACTION;
GO
