T-SQL：在更新触发器中 CLOSE/DEALLOCATE 游标的正确方法

Question

假设我有一个这样的触发器：

CREATE TRIGGER trigger1
   ON [dbo].[table1] 
   AFTER UPDATE
AS 
BEGIN               
    --declare some vars
    DECLARE @Col1 SMALLINT 
    DECLARE @Col1 TINYINT 

    --declare cursor        
    DECLARE Cursor1 CURSOR FOR 
    SELECT Col1, Col2 FROM INSERTED             

    --do the job
    OPEN Cursor1
    FETCH NEXT FROM Cursor1 INTO @Col1, @Col2

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF ...something...
        BEGIN           
            EXEC myProc1 @param1 = @Col1, @Param2 = @Col2
        END             
        ELSE
        IF ...something else...
        BEGIN           
            EXEC myProc2 @param1 = @Col1, @Param2 = @Col2
        END     

        FETCH NEXT FROM Cursor1 INTO @Col1, @Col2               
    END

    --clean it up       
    CLOSE Cursor1
    DEALLOCATE Cursor1                  
END

我想确保 Cursor1 始终关闭并释放。即使 myProc1 或 myProc2 也会失败。

我应该使用 try/catch 块吗？

Answer 1

您可以使用 CURSOR_STATUS() 函数。

if CURSOR_STATUS('global','cursor_name') >= 0 
begin
 close cursor_name
  deallocate cursor_name 
end

参考：http://msdn.microsoft.com/ en-us/library/ms177609.aspx

Answer 2

是的，使用 TRY/CATCH 但请确保在之后取消分配等。
不幸的是，SQL Server 中没有finally。

但是，我建议将其包装在另一个 try/catch 中，

CREATE TRIGGER trigger1 ON [dbo].[table1] AFTER UPDATE
AS 
BEGIN                           
    --declare some vars
    DECLARE @Col1 SMALLINT, @Col1 TINYINT 

    BEGIN TRY
        --declare cursor            
        DECLARE Cursor1 CURSOR FOR 
        SELECT Col1, Col2 FROM INSERTED                     

        --do the job
        OPEN Cursor1
        FETCH NEXT FROM Cursor1 INTO @Col1, @Col2

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF ...something...
                    EXEC myProc1 @param1 = @Col1, @Param2 = @Col2
            ELSE
            IF ...something else...
                    EXEC myProc2 @param1 = @Col1, @Param2 = @Col2

            FETCH NEXT FROM Cursor1 INTO @Col1, @Col2                               
        END
    END TRY
    BEGIN CATCH
        --do what you have to
    END CATCH

    BEGIN TRY
        --clean it up               
        CLOSE Cursor1
        DEALLOCATE Cursor1                                  
    END TRY
    BEGIN CATCH
        --do nothing
    END CATCH
END

触发器中的游标是否是一个好主意是另一回事......

Answer 3

十年后，我认为我应该为这个特定问题添加一些信息。

您的问题有两个主要解决方案。首先，使用 LOCAL 游标声明：

DECLARE --Operation
    Cursor1 -- Name
CURSOR -- Type
    LOCAL READ_ONLY FORWARD_ONLY -- Modifiers
FOR -- Specify Iterations
SELECT Col1, Col2 FROM INSERTED;

这将您的特定游标仅限于活动会话，而不是服务器的全局上下文，假设没有其他操作调用此游标。原则上类似的是使用游标变量，它看起来像这样：

DECLARE @Cursor1 CURSOR;
SET @Cursor1 = CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT Col1, Col2 FROM INSERTED;

在使用游标变量时，除了将范围管理在范围内之外，您还可以随时使用 SET 语法覆盖它您的特定会话就像前面的示例一样。通过覆盖游标上下文，您可以有效地释放它拥有的任何过去的引用。也就是说，这两种方法都通过将光标的状态链接到当前连接的活动来实现您的初衷。

如果您的应用程序上下文使用连接池，这可能会留下延迟锁，在这种情况下，您应该使用 Try-Catch 模式，如下所示：

CREATE TRIGGER trigger1
   ON [dbo].[table1] 
   AFTER UPDATE
AS 
BEGIN               
    --declare some vars
    DECLARE @Col1 SMALLINT;
    DECLARE @Col2 TINYINT;

    --declare cursor        
    DECLARE 
        Cursor1 
    CURSOR 
        LOCAL READ_ONLY FORWARD_ONLY 
    FOR 
        SELECT 
            Col1, 
            Col2 
        FROM 
            INSERTED;

    --do the job
    OPEN Cursor1;

    BEGIN TRY

        FETCH 
            NEXT 
        FROM 
            Cursor1 
        INTO 
            @Col1, 
            @Col2;

        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF -- my condition
                    EXEC myProc1 @param1 = @Col1, @Param2 = @Col2;
                ELSE IF -- additional condition
                    EXEC myProc2 @param1 = @Col1, @Param2 = @Col2;

                FETCH 
                    NEXT 
                FROM 
                    Cursor1 
                INTO 
                    @Col1, 
                    @Col2;
            END;
    END TRY

    BEGIN CATCH
        -- Error Handling
    END CATCH

    --clean it up       
    CLOSE Cursor1;
    DEALLOCATE Cursor1;
END;

以这种方式使用该模式可以减少代码重复，或者需要检查光标的状态。基本上，游标初始化应该是安全的，就像 open 语句一样。一旦游标打开，您将希望始终从会话中关闭它并取消分配它，并且假设游标已打开，这应该始终是一个安全的操作（我们刚刚建立的应该始终是一个安全的操作）。因此，将它们留在 Try-Catch 范围之外意味着所有内容都可以在 Catch 块之后的最后整齐地关闭。

值得一提的是，我指定了游标的 READ_ONLY 属性以及 FORWARD_ONLY 属性，因为您的示例代码没有在集合中的记录之间来回滚动。如果您要修改这些过程中的基础行，那么最好使用 STATIC 游标，以确保不会意外导致无限循环。这应该不是问题，因为您使用 INSERTED 表来管理光标上下文，但对于其他潜在用例仍然值得一提。

如果您想了解有关 SQL Server 中游标的更多信息，我强烈建议您阅读 这篇关于该主题的博客文章，他详细解释了游标的各种修饰符是什么，以及它们在数据库引擎中的效果。

Answer 4

您应该做的是永远不要在触发器中使用游标。而是编写正确的基于集合的代码。如果有人将数据导入到包含 100,000 条新记录的表中，您将锁定该表数小时，并使数据库彻底停止运行。在触发器中使用游标是一种非常糟糕的做法。