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

Question

Hello Everyone,

This is my first question on StackOverflow, so please be nice ;)

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.

Shall I use try/catch block?

Thanks, Kamil

Answer 1

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 reiords 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.

Answer 2

Required reading.

Answer 3

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

However, I suggest wrapping this in another 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 4

You could use the CURSOR_STATUS() function.

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