T-SQL: правильный способ курсора CLOSE/DEALLOCATE в триггере обновления

Ответ 1

Да, используйте TRY/CATCH, но убедитесь, что вы освобождаете и т.д. после. К сожалению, в SQL Server окончательно нет.

Однако я предлагаю обернуть это в другой 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

Является ли курсор в триггере хорошей идеей, это другое дело...

Ответ 2

Вы можете использовать функцию 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

Ответ 3

То, что вы должны делать, никогда не использует курсор в триггере. Вместо этого напишите правильный код на основе набора. Если бы кто-то импортировал данные в вашу таблицу из 100 000 новых записей, вы бы запирали таблицу в течение нескольких часов и приводили вашу базу к кричащей остановке. Очень плохо использовать курсор в триггере.

Ответ 4

Десять лет спустя, я полагаю, я должен добавить некоторую информацию к этому конкретному вопросу.

Есть два основных решения вашей проблемы. Во-первых, используйте объявление LOCAL курсора:

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

Это ограничивает ваш конкретный курсор только вашим активным сеансом, а не глобальным контекстом сервера, при условии, что никакое другое действие не вызывает этот курсор. Аналогично в принципе использовать переменную Cursor, которая будет выглядеть следующим образом:

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

Используя переменную курсора, вы всегда можете перезаписать ее в любое время, используя синтаксис SET, в дополнение к управлению областью, чтобы она находилась в вашем конкретном сеансе, как в предыдущем примере. Перезаписывая контекст курсора, вы фактически освобождаете любую прошлую ссылку, которая у него была. Тем не менее, оба эти подхода реализуют ваше первоначальное намерение, связывая состояние курсора с активностью текущего соединения.

Это может оставить длительную блокировку, если в контексте вашего приложения используется пул соединений, и в этом случае вы должны использовать шаблон 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;

Использование шаблона таким образом уменьшает дублирование кода или необходимость проверки состояния курсора. По сути, инициализация курсора должна быть безопасной, как и оператор open. Как только курсор открыт, вы захотите всегда закрывать-освобождать его из сеанса, и это всегда должно быть безопасным действием при условии, что курсор был открыт (который мы только что установили, всегда должен быть безопасной операцией). Таким образом, выход за пределы границ Try-Catch означает, что все может быть аккуратно закрыто в конце, после блока Catch.

Стоит отметить, что я указал атрибут READ_ONLY для курсора, а также FORWARD_ONLY, так как ваш пример кода не прокручивался назад и вперед между записями в наборе. Если вы изменяете базовые строки в этих процедурах, вам, вероятно, лучше использовать курсор STATIC чтобы избежать случайного возникновения бесконечного цикла. Это не должно быть проблемой, так как вы используете таблицу INSERTED для управления контекстом курсора, но все же стоит упомянуть о других потенциальных случаях использования.

Если вы хотите узнать больше о курсорах в SQL Server, я настоятельно рекомендую прочитать этот пост в блоге на эту тему, поскольку он очень подробно объясняет, каковы различные модификаторы курсора и как они влияют на компонент Database Engine.