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

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

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 UPDATEAS 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 CATCHEND

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

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 LOCAL cursor declaration:

DECLARE --Operation    Cursor1 -- NameCURSOR -- Type    LOCAL READ_ONLY FORWARD_ONLY -- ModifiersFOR -- Specify IterationsSELECT 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 SET syntax, 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.

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

CREATE TRIGGER trigger1   ON [dbo].[table1]    AFTER UPDATEAS 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-Catch means that everything can be neatly closed at the end, after the Catch block.

It's worth mentioning that I specified the READ_ONLY attribute 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 STATIC cursor to ensure you don't accidentally cause an infinite loop. That shouldn't be a problem since you're using the INSERTED table to manage your cursor context, but still worth mentioning for other potential use cases.

If you want to learn more about cursors in SQL Server, I highly recommend reading this blog post on 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.