T-SQL: A proper way to CLOSE/DEALLOCATE cursor in the update trigger
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...
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