Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
вопроспопроблеме
Guest
Ситуация такая. Висят 2 update-триггера на таблице. Один обновляет 2 поля (время обновления и автор обновления) при любых изменениях. Другой обновляет несколько полей-флагов в зависимости от того были ли изменены определенные поля.

первый триггер:

ALTER TRIGGER [dbo].[tuht_MyTable] on [dbo].[MyTable] 
	FOR UPDATE AS
BEGIN
	IF NOT EXISTS(SELECT * FROM inserted) 
		RETURN
      
	UPDATE MyTable SET
		MyTable.PKDP_HKA = SUSER_SNAME(),
		MyTable.PKDP_TSA = GETDATE()
	FROM MyTable, inserted it
	WHERE MyTable.PKDP_ID = it.PKDP_ID
END


и второй триггер:

ALTER TRIGGER [dbo].[tu_MyTable] on [dbo].[MyTable]
	FOR UPDATE AS
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS(SELECT * FROM inserted) 
		RETURN

	IF (SUSER_SNAME() IN ('sa', 'xxx', 'zzz'))
	BEGIN
		UPDATE MyTable SET 
			Field1 = CASE ... THEN 1 ELSE 0 END
			...
			FieldN = CASE ... THEN 1 ELSE 0 END
		FROM MyTable t
			INNER JOIN inserted i ON t.PKDP_ID = i.PKDP_ID 
			INNER JOIN deleted d ON i.PKDP_ID = d.PKDP_ID 
	END
END


Не регулярно и не при всех изменениях выскакивает ошибка:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Сначала вместо IF NOT EXISTS(SELECT * FROM inserted) стояла проверка на @@rowcount, потом прочел в MSDN, что использование SET NOCOUNT ON сбрасывает @@rowcount в 0. Изменил @@rowcount на inserted. Пол дня все было нормально, сейчас опять ошибка выскакивает.

Версия сервера: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1797.0 (X64) Jun 1 2011 15:43:18 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Подскажите куда копать.
Спасибо!
11 апр 13, 14:31    [14167277]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
Гость333
Member

Откуда:
Сообщений: 3683
вопроспопроблеме
Подскажите куда копать.

В сторону объединения двух триггеров в один?
11 апр 13, 14:39    [14167339]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
если "Не регулярно и не при всех изменениях ", то вы не там ищете проблему.
если бы дело было в рекурсии триггеров, то, как минимум, первый триггер - в том виде, в котором он показан здесь - уходил бы в бесконечную (до ошибки про 32 уровня) рекурсию всегда.

или давайте уже реальный код показывайте.
11 апр 13, 14:39    [14167340]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
Гость333
Member

Откуда:
Сообщений: 3683
daw
если бы дело было в рекурсии триггеров, то, как минимум, первый триггер - в том виде, в котором он показан здесь - уходил бы в бесконечную (до ошибки про 32 уровня) рекурсию всегда.

Хм... так тут вроде неявная рекурсия — т.е. опция RECURSIVE_TRIGGERS отключена, а nested triggers — включена. И триггеры поочерёдно вызывают друг друга.
11 апр 13, 14:42    [14167369]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
вопроспопроблеме
Guest
daw
если "Не регулярно и не при всех изменениях ", то вы не там ищете проблему.
если бы дело было в рекурсии триггеров, то, как минимум, первый триггер - в том виде, в котором он показан здесь - уходил бы в бесконечную (до ошибки про 32 уровня) рекурсию всегда.
или давайте уже реальный код показывайте.


Это реальный код, только таблица с полями переименованы и во втором триггере сокращена инструкция update (в том месте где значения присваиваются полям).

"Не регулярно" - я не совсем правильно выразился. После того, как я ее поправил, несколько апдейтов отработали без ошибок (где-то с десяток раз протестировал), а сегодня опять тоже самое.
11 апр 13, 14:50    [14167423]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
вопроспопроблеме
Guest
Гость333
вопроспопроблеме
Подскажите куда копать.

В сторону объединения двух триггеров в один?


Не получится. "Политика партии" такова, что 1й триггер - "стандартный" и висит практически на всех таблицах. Второй же касается только этой. Объединять их не будут, чтоб не ломать единообразие.
11 апр 13, 14:53    [14167445]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Гость333,

ээээ. да, действительно.

автору: может TRIGGER_NESTLEVEL еще поможет.
if trigger_nestlevel(@@procid, 'AFTER', 'DML') > 1 return;

но да - объединить лучше, наверное.
11 апр 13, 14:53    [14167448]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
sp_set_triggerorder ?
11 апр 13, 14:54    [14167457]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Maxx
sp_set_triggerorder ?

хрень написал ,надо @@nestLevel прерять,если он глужбже чем надо - выходить из триггера
11 апр 13, 14:56    [14167475]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
автор
Recursive triggers enable the following types of recursion to occur:

Indirect recursion

With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.


Direct recursion

With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.


The following example uses both indirect and direct trigger recursion Assume that two update triggers, TR1 and TR2, are defined on table T1. Trigger TR1 updates table T1 recursively. An UPDATE statement executes each TR1 and TR2 one time. Additionally, the execution of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deleted tables for a specific trigger contain rows that correspond only to the UPDATE statement that invoked the trigger.

Note:
The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is enabled by using ALTER DATABASE. There is no defined order in which multiple triggers defined for a specific event are executed. Each trigger should be self-contained.



Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.




Видимо триггеры неявно друг друга вызывают. можно попробовать как тут замечалось ранее проставить nested triggers в 0 или сделать один триггер вместо двух.
11 апр 13, 15:07    [14167549]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка: Maximum procedure, function, trigger, or view nesting level exceeded  [new]
вопроспопроблеме
Guest
TRIGGER_NESTLEVEL вроде помог!

Всем спасибо!
11 апр 13, 15:29    [14167739]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить