MS SQL Server Management Studio中处理事务的最佳方法

Question

假设我有一个语法和语义都正确的SQL语句，那么它会被执行。

在管理工具（或任何其他查询工具）中，如何测试SQL语句？如果我发现它们破坏了某些内容，该如何回退事务（在单独的查询中）？

注：SQL语句是指Structured Query Language（结构化查询语言），一种用于管理关系数据库系统的标准编程语言。

- Niels Bosma

Answer 1

最简单的方法是将你的代码放入一个事务中，然后逐行执行每个批次的T-SQL代码。

例如:

Begin Transaction

         -Do some T-SQL queries here.

Rollback transaction -- OR commit transaction

如果您想要加入错误处理，可以使用 TRY...CATCH 块。如果发生错误，您可以在 catch 块中回滚事务。

例如：

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

请参阅以下链接以了解更多详细信息。

http://msdn.microsoft.com/en-us/library/ms175976.aspx

- John Sansom

---

你好，谢谢。我第一次在这里看到@@TRANCOUNT，你能告诉我在回滚处理后"IF @@TRANCOUNT > 0 COMMIT TRANSACTION"会发生什么？以及@@TRANCOUNT有什么值？再次感谢。 嗨，谢谢。我第一次在这里了解到@@TRANCOUNT，你能告诉我在ROLLBACK处理之后，“IF @@TRANCOUNT>0 COMMIT TRANSACTION”会发生什么？@@TRANCOUNT的值是多少？谢谢。 - QMaster

执行ROLLBACK TRANSACTION后，@@TRANCOUNT将被重置为0。因此，COMMIT TRANSACTION将不会被执行。 请参阅https://msdn.microsoft.com/de-de/library/ms187967.aspx。 - Christoph Brückmann

Answer 2

我想补充一点，如果你写的内容很复杂，你也可以（并且应该）添加一个测试变量来回滚。这样你就可以一次性执行整个操作。通常我还会添加代码来查看各种操作的前后结果，尤其是对于复杂的脚本。

以下是示例：

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

- HLGEM