tsql - T-SQL : A proper way to CLOSE/DEALLOCATE cursor in the update trigger

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 block 吗？

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