Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 засада с рекурсией  [new]
АлексейК
Member

Откуда: http://www.msdatabase.ru , Moscow
Сообщений: 7683
mssql 2005 включил рекурсивные триггра
табличка price содержит ценники на товары, некоторые из товаров носят составной характер
их состав определяется в табличке dbo.price_Compound
сейчас максимальный уровень вложенности введенных данных = 3

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

может кто глянет незамыленным взглядом:

ALTER TRIGGER [price_Trigger_productPrice] 
   ON  [dbo].[price]
   AFTER  INSERT,DELETE,UPDATE
AS 
BEGIN
SET NOCOUNT ON
    update  dbo.price set  CostPrice = 
      isnull((select sum(pc.quantity * p.NominalPrice)
        from dbo.price_Compound pc inner join 
             dbo.price p on pc.PriceID = p.id 
      where pc.CompoundPriceID = pr.ID ),0)

 from dbo.price pr inner join 
  dbo.price_Compound pcc on pr.id = pcc.CompoundPriceID inner join
(select id from inserted union all select id from deleted ) i on pcc.priceid = i.id
END
20 янв 07, 15:58    [3670455]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
АлексейК
Member

Откуда: http://www.msdatabase.ru , Moscow
Сообщений: 7683
попробовал переделать так:
ALTER TRIGGER [price_Trigger_productPrice] 
   ON  [dbo].[price]
   AFTER  INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON
 
    update  dbo.price set  CostPrice = 
isnull((select sum(pc.quantity * p.NominalPrice)
        from dbo.price_Compound pc inner join 
             dbo.price p on pc.PriceID = p.id 
      where pc.CompoundPriceID = pr.ID ),0)

 from dbo.price pr inner join 
  dbo.price_Compound pcc on pr.id = pcc.CompoundPriceID inner join
(select id from inserted union select id from deleted ) i on pcc.priceid = i.id

where 
CostPrice <> isnull((select sum(pc.quantity * p.NominalPrice)
        from dbo.price_Compound pc inner join 
             dbo.price p on pc.PriceID = p.id 
      where pc.CompoundPriceID = pr.ID ),0)

END

не помогло...
20 янв 07, 16:15    [3670475]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
Luchkin Dmitry
Member

Откуда: Новосибирск -> Ангарск
Сообщений: 1919
создайте временные таблицы вместо inserted & deleted, киньте в них пару десятков строк (можете в триггере их заполнить), потом выполните свой скрипт больше одного раза.
при повторном вызове должно быть @@rowcount = 0
20 янв 07, 17:19    [3670549]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
АлексейК
Member

Откуда: http://www.msdatabase.ru , Moscow
Сообщений: 7683
непонял идею.
даже если я сделаю вставку в какую небуть тествую табличку из inserted и deleted
у меня там ничего не сохранится ибо при любых модификациях в исходнй таблице происходит ошибка превышения вложенности 32 и откат транзакции
20 янв 07, 17:23    [3670553]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
Luchkin Dmitry
Member

Откуда: Новосибирск -> Ангарск
Сообщений: 1919
идея проста.
триггер нужно, конечно, отключить. а запрос для теста прогнать руками дважды, проверяя после @@rowcount. повторно запрос не должен менять ни одной записи, т.к. именно повторное изменение приводит к рекурсии. во втором посте вы добавили в where условие для недопущения такого. так вот оно не правильное (недостаточное). будет верное, - не будет превышения.
20 янв 07, 17:33    [3670561]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
АлексейК
Member

Откуда: http://www.msdatabase.ru , Moscow
Сообщений: 7683
так в условии я просто скопировал то что обновляется
тип данных везде денежный
навсякий случай сделал приведение явное к денежному типу та же хрень.

попробую ваш совет
20 янв 07, 17:42    [3670572]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
Luchkin Dmitry
Member

Откуда: Новосибирск -> Ангарск
Сообщений: 1919
Вы-б сделали внутренний запрос нормально, чтоб он возвращал таблицу ид - сумма, тогда
update baseTable set Cost= tpr.NewCost
from baseTable
inner join [Полный_запрос_с_промежуточными_расчётами] tpr on tpr.ID = baseTable.ID
where baseTable.Cost <> tpr.NewCost
а в ручном выполнении запроса тога можно будет вывести Cost и NewCost и посмотреть, чем они отличаются тогда.
20 янв 07, 17:50    [3670580]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
АлексейК
Member

Откуда: http://www.msdatabase.ru , Moscow
Сообщений: 7683
сделал проверку так:
1 отключил рекурсию в базе
2
ALTER TRIGGER [price_Trigger_productPrice] 
   ON  [dbo].[price]
   AFTER  INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON
if update(CostPrice)  or update(ProductKoef)
    update  dbo.price set  CostPrice = 
cast (isnull((select sum(pc.quantity * p.NominalPrice)
        from dbo.price_Compound pc inner join 
             dbo.price p on pc.PriceID = p.id 
      where pc.CompoundPriceID = pr.ID ),0) as money)

 from dbo.price pr inner join 
  dbo.price_Compound pcc on pr.id = pcc.CompoundPriceID inner join
(select id from inserted union select id from deleted ) i on pcc.priceid = i.id

where 
pr.CostPrice <> cast (isnull((select sum(pc.quantity * p.NominalPrice)
        from dbo.price_Compound pc inner join 
             dbo.price p on pc.PriceID = p.id 
      where pc.CompoundPriceID = pr.ID ),0) as money)

declare @re as varchar(50)
set @re = @@rowcount
 
RAISERROR  ( @re , 16,1)

END

если я меняю значение ProductKoef на другое то если у товара есть родитель выдает 1 в ошибке
если я делаю апдейт ProductKoef на тоже самое выдает 0

насколько я понимаю это значит что условие верное
и еще шняга то! если рекурсия включена то ошибка RAISERROR ( @re , 16,1) не выдается а сразу говорит о превышении уровня вложенности!!! что это????
20 янв 07, 17:59    [3670588]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
Luchkin Dmitry
Member

Откуда: Новосибирск -> Ангарск
Сообщений: 1919
может, сможете найти другой триггер, в котором зацикливание?
20 янв 07, 18:17    [3670606]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
АлексейК
Member

Откуда: http://www.msdatabase.ru , Moscow
Сообщений: 7683
триггер всего один на этой таблице
20 янв 07, 18:28    [3670615]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
АлексейК
Member

Откуда: http://www.msdatabase.ru , Moscow
Сообщений: 7683
есть вычисляемое поле но оно я думаю не должно мешать
20 янв 07, 18:29    [3670618]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
if object_id('test') is not null drop table test
go
create table test(id int identity, x int default(0))
go
create trigger t_test on test for insert, update, delete as
begin
 print @@nestlevel
 update test set x=id where 1=0 -- триггер вызывается на команду update, а не на изменение данных
end
go
insert test(x) default values
20 янв 07, 20:16    [3670784]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
iap
Member

Откуда: Москва
Сообщений: 46952
АлексейК
mssql 2005 включил рекурсивные триггра
табличка price содержит ценники на товары, некоторые из товаров носят составной характер
их состав определяется в табличке dbo.price_Compound
сейчас максимальный уровень вложенности введенных данных = 3

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

может кто глянет незамыленным взглядом:

ALTER TRIGGER [price_Trigger_productPrice] 
   ON  [dbo].[price]
   AFTER  INSERT,DELETE,UPDATE
AS 
BEGIN
SET NOCOUNT ON
    update  dbo.price set  CostPrice = 
      isnull((select sum(pc.quantity * p.NominalPrice)
        from dbo.price_Compound pc inner join 
             dbo.price p on pc.PriceID = p.id 
      where pc.CompoundPriceID = pr.ID ),0)

 from dbo.price pr inner join 
  dbo.price_Compound pcc on pr.id = pcc.CompoundPriceID inner join
(select id from inserted union all select id from deleted ) i on pcc.priceid = i.id
END

Объясните, где здесь задана связь между изменяемыми записями dbo.price и псевдотаблицами deleted и inserted?
Имхо, Вы пытаетесь апдейтить все без исключения записи dbo.price. Это так и было задумано? Не могли бы Вы изложить, по какому алгоритму надо апдейтить записи в данном триггере?
20 янв 07, 20:26    [3670791]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
АлексейК
Member

Откуда: http://www.msdatabase.ru , Moscow
Сообщений: 7683
спасибо!!!
точно.

попробую
так:
if (select count(*) from inserted)+ (select count(*) from deleted) > 0
update ...
20 янв 07, 20:31    [3670792]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
АлексейК
Member

Откуда: http://www.msdatabase.ru , Moscow
Сообщений: 7683
еще раз всем спасибо, все получилось
20 янв 07, 20:41    [3670800]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
iap
Member

Откуда: Москва
Сообщений: 46952
iap
Имхо, Вы пытаетесь апдейтить все без исключения записи dbo.price.

Простите, глупость написал.
20 янв 07, 20:42    [3670801]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
iap
Member

Откуда: Москва
Сообщений: 46952
АлексейК
спасибо!!!
точно.

попробую
так:
if (select count(*) from inserted)+ (select count(*) from deleted) > 0
update ...

Это проверяется проще:
IF @@ROWCOUNT>0 UPDATE ...
Только убедитесь, что перед проверкой @@ROWCOUNT внутри триггера не было SELECT, DELETE, UPDATE и т.п., которые влияют на значение этой системной переменной.
20 янв 07, 20:46    [3670807]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
или (более "безопасное" чем @@rowcount)
if exists(select * from inserted) or exists(select * from deleted)
т.к. exists специально "заточено" для проверки сущетсвования записей, то можно надеяться на большую эффективность чем
if (select count(*) from inserted)+ (select count(*) from deleted) > 0
20 янв 07, 21:54    [3670869]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: засада с рекурсией  [new]
UncleFedor32
Member

Откуда:
Сообщений: 30
Есть ли способ увеличить максимальное число уровней вложенности?
Написал рекурсивную функцию, которая точно будет вызывать сама себя больше чем 32 раза (до пары сотен раз). Рекурсия конечна, проверено на случаях с менее чем 32 вызовами.

Заранее благодарен
8 окт 17, 15:28    [20852209]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
maxrecursion
8 окт 17, 15:42    [20852230]     Ответить | Цитировать Сообщить модератору
 Re: засада с рекурсией  [new]
aleks222
Guest
Гавриленко Сергей Алексеевич
maxrecursion

Да ну?!!

UncleFedor32
Есть ли способ увеличить максимальное число уровней вложенности?
Написал рекурсивную функцию, которая точно будет вызывать сама себя больше чем 32 раза (до пары сотен раз). Рекурсия конечна, проверено на случаях с менее чем 32 вызовами.

Заранее благодарен


Не благодарите.
1. Низзя.
2. Не надо "рекурсивных функций" - плохо это.
3. Напрягись и напиши цикл.
8 окт 17, 16:03    [20852263]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить