T-SQL: правильный способ курсора CLOSE/DEALLOCATE в триггере обновления
Скажем, у меня есть триггер:
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?
Ответы
Ответ 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.