database T-SQL：在更新触发器中关闭/释放游标的正确方法

Question

提问by Novitzky

Let's say I've got a trigger like this:

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

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

I want to be sure that Cursor1 is always closed and deallocated. Even myProc1 or myProc2 fails.

我想确保 Cursor1 总是关闭和解除分配。甚至 myProc1 或 myProc2 也会失败。

Shall I use try/catch block?

我应该使用 try/catch 块吗？

Answer 1

采纳答案by gbn

Yes, use TRY/CATCH but make sure you deallocate etc after. Unfortunately, there is no finally in SQL Server.

是的，使用 TRY/CATCH 但确保在之后释放等。不幸的是，SQL Server 中没有 finally。

However, I suggest wrapping this in another try/catch

但是，我建议将其包装在另一个 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

Whether a cursor in a trigger is a good idea is a different matter...

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

Answer 2

回答by

You could use the CURSOR_STATUS() function.

您可以使用 CURSOR_STATUS() 函数。

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

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

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

Answer 3

回答by HLGEM

What you should do is never ever use a cursor in a trigger. Write correct set-based code instead. If someone did an import of data into your table of 100,000 new records you would lock up the table for hours and bring your database to a screaming halt. It is a very poor practice to use a cursor in a trigger.

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

Answer 4

回答by Solonotix

Ten years later, I figure I should add some information to this particular question.

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

There are two primary solutions to your problem. First, use a LOCALcursor declaration:

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

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

This limits your particular cursor to only your active session, rather than the global context of the server, assuming no other action is calling into this cursor. Similar in principle is to use a Cursor Variable, which would look like this:

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

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

In using a cursor variable, you can always overwrite it at anytime using the SETsyntax, in addition to managing the scope to being within your particular session like the previous example. By overwriting the cursor context, you effectively deallocate any past reference it had. That said, both of these approaches accomplish your original intention by linking the status of the cursor to the activity of the current connection.

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

This might leave a lingering lock if your app context is using Connection Pooling, in which case you should use the Try-Catchpattern as follows:

如果您的应用程序上下文使用连接池，这可能会留下一个挥之不去的锁定，在这种情况下，您应该使用以下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;

Using the pattern in this way reduces the code duplication, or need to check the status of the cursor. Basically, the Cursor-initialization should be safe, as is the open statement. Once the cursor is open, you will want to always close-deallocate it from the session, and that should always be a safe action assuming the cursor has been opened (which we just established should always be a safe operation). As such, leaving those outside the confines of the Try-Catchmeans that everything can be neatly closed at the end, after the Catchblock.

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

It's worth mentioning that I specified the READ_ONLYattribute of the cursor, as well as FORWARD_ONLY, since your sample code didn't scroll back-and-forth between records in the set. If you are modifying the underlying rows in those procedures, you are probably better off using a STATICcursor to ensure you don't accidentally cause an infinite loop. That shouldn't be a problem since you're using the INSERTEDtable to manage your cursor context, but still worth mentioning for other potential use cases.

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

If you want to learn more about cursors in SQL Server, I highly recommend reading this blog poston the subject, as he goes into great detail explaining what the various modifiers of a cursor are, and the effects they have within the Database Engine.

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

database T-SQL：在更新触发器中关闭/释放游标的正确方法

提问by Novitzky

采纳答案by gbn

回答by

回答by HLGEM

回答by Solonotix

相关推荐

最近更新

标签

database T-SQL：在更新触发器中关闭/释放游标的正确方法

提问by Novitzky

采纳答案by gbn

回答by

回答by HLGEM

回答by Solonotix

相关推荐

database 用于捕获审计跟踪的数据库设计的想法

database 在数据库中存储密码的最佳方法

database 您最喜欢的数据库客户端工具

database 万维网最大的站点在哪些数据库上运行？

相关推荐

最近更新

标签