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

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Всем доброе время суток!

Пытаюсь найти хоть какой-то код для триггера для автоматического обновления HierarchyID.

Идя классическая "была таблица обычная иерархическая с pk/fkParent а надо сделать так что бы на основе старой логике вычислялось значение поля с типом HierarchyID".

Причём хотелось бы что бы я не задумывался сколько строчек вставляется, что они могут вставляться и в разброс между старых строчек и несклолько штук подряд между двумя старыми строчками.

Метод "в тупую" - при апдтейте полей связанных с этой логикой или инсёрте новых строк тупо запускать процедуру которая перестраивает справочник.

Идея "более разумная" - определять родителей до которых идут изменения и "пересчитывать только нужные ветки".

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

В общем ищу готовое и красивое решение! Заранее спасибо!
13 ноя 13, 18:59    [15124159]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
NIIIK, Populating a Table with Existing Hierarchical Data ни?
13 ноя 13, 19:44    [15124285]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
вроде там изначально задаются значения, а не поддерживаются триггером?
13 ноя 13, 20:03    [15124345]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
В анналах форума я кидал примеры триггеров. Поищите.
Но как я помню универсальных решений нет, зависит от принципа вставки и ограничений на таблице.

К примеру если у вас HierarchyID уникальное поле, то как вы будете прозрачно вставлять данные?
13 ноя 13, 21:01    [15124580]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
7713554 Что-то больше найти не могу, а там явно не до конца и немного не то, мягко говоря.
13 ноя 13, 21:26    [15124630]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Mnior,

Я находил это, даже немного (дже много переделал) и апдейт дописал, но это будет работать если одному родителю будет вставлять только одна запись.
13 ноя 13, 21:37    [15124653]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
with 

--Initial values that must be defined by trigger logic based on inserted/deleted tables values and additional conditions.
cteInsertedTr as
(
select * 
  from dbo.tCommonDictionary 
 where PK 
  --in (2108, 2111, 5138, 4, 5137, 5136)
    in ( 5138,  5137, 2111, 2108)
)
,

--define parents for updated attributes because I can't insert more than one row between existing rows 
--all child records will be recomputed and we will not have value between /999/1/ and /997/2/
--it's minus of logic especially when we need to update root rows !!!
--because field hrchID for whole dictionary will be recomputed 
cteInserted as
(
select distinct 
       v.* 
  from cteInsertedTr t
  left
  join dbo.tCommonDictionary v
    on v.PK = t.fkOwner
)
,

--build tree from child to parent row
--because we will need to use one rows (branch) one time even if it's included more then once (as child and as leaf) in cteInsertedTr
cteTreeUp as
(
select start.PK as start_PK,
       start.PK,
       start.fkOwner,       
       start.name,
       1 as initialLeaf
  from cteInserted start

union all

select start.PK as start_PK,
       v.PK,
       v.fkOwner,       
       v.name,
       0 as initialLeaf
  from dbo.tCommonDictionary v
 inner
  join cteTreeUp start
     on v.PK = start.fkOwner    
)
,

/*
If some record has parent record as LEAF that should be updated then be use parent record only
for exampe


PK, pk_parent vendor
5136, null
5137, 5136
5138, 5137

if we pass 5136, 5137, 5138 then 5137, 5138 must be excluded

*/
cteShouldBeExcluded as
(
select distinct 
       t1.start_PK
  from cteTreeUp t1
 inner
  join cteTreeUp t2
    on t2.PK = t1.fkOwner 
   and t2.initialLeaf = 1

)
,
cteShouldBeUpdated as
(
select i.hrchID,
       i.PK
  from cteInserted i
  left
  join cteShouldBeExcluded exc
    on i.PK = exc.start_PK
 where exc.start_PK is null
 
 /*
union all
select null, null
 where 1 <> 1 -- when we need to rebuld all rows
*/
 
)
,

cteTree as 
(
select v_child.PK,
       v_child.fkOwner,
       cast(coalesce(cast(sbu.hrchID as nvarchar(100)), '/') + cast(row_number() over (order by v_child.name) as nvarchar(30)) + '/' as hierarchyid) as hrchID       
  from cteShouldBeUpdated sbu

  --left
  join dbo.tCommonDictionary v_child
    on (v_child.fkOwner = sbu.PK or sbu.PK is null and v_child.fkOwner is null)

union all 

select v_child.PK,
       v_child.fkOwner,
       cast(t.hrchID.ToString() + cast(row_number() over (order by  v_child.name) as nvarchar(30)) + '/' as hierarchyid) as hrchID     
  from cteTree t

  --left
  join dbo.tCommonDictionary v_child
    on (v_child.fkOwner = t.PK)

   
)

select *,
       CAST(hrchID as nvarchar)
  from cteTree    


Вот такой принцип только сейчас "осознаваем".
У него недостатки в духе "пересчитывает все дочернии записи того же уровня", т. е. по факту на вход подаётся не сама извенённая запись, а её родитель.
Я просто "в данном состоянии мозга" не могу организовать так, что бы если более одной записи на одном уровне были изменены, то каждая из них вклинивается на своё место между существующими. Ну дочерние записи перестраивать - это "полюбому".
Завтра утром (или когда вариант будет) попробую найти время подумать как "вклинивать" записи при помощи row_number и порядка который должен быть.

А вообще с данной хренью конечно проще когда одной процедурой работаешь :)
14 ноя 13, 00:34    [15125160]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mnior
универсальных решений нет, зависит от принципа вставки и ограничений на таблице.
К примеру если у вас HierarchyID уникальное поле, то как вы будете прозрачно вставлять данные?
NIIIK, вы должны задать для начала правила игры.
INSTEAD OF триггера могут банально не подойти под ограничения.
AFTER не подходит для уникальности поля HierarchyID, от которой я бы не советовал отказываться.
Обрабатывать ли цикличность?
И вообще такой подход - нагрузка на сервер, это лучше на клиенте делать.

Если у вас итак всё пересчитывается, то должно быть просто, т.к. "пути" будут всегда "чистыми" - ровно пронумерованы, простая конкатинация строк.
А обновлять нужно все ноды в глубь, начиная от "братьев" изменённых нод (удалены, вставлены, переставлены-отсортированы). Рекурсивно.
Всё.
15 ноя 13, 10:38    [15133906]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Mnior,

Я в принципе планирую даже не instead of, а обычный after insert/update.

Предыдущее значение иерархического поля можно подумать "использовать ли для выборки что бы переапдейтить".

Последовательность сама по себе не важна, её в целом можно "преодолеть", вот таким запросом

with 

cteInsertedTr as
(
select * 
  from dbo.tCommonDictionary 
 where pk 
  in (2108, 2111, 5138, 4, 5137, 5136, 2844)
  --in ( 5138,  5137, 2111, 2108)
)
,

cteChangedParents as
(
select distinct
       fkOwner
  from cteInsertedTr
)
,

cteOrderedByParents as
(
select row_number() over (partition by chP.fkOwner order by v.name, pk) as rowNum,
       v.* 
  from cteChangedParents chP
 inner
  join dbo.tCommonDictionary v
    on chP.fkOwner = v.fkOwner
	   or chP.fkOwner is null and v.fkOwner is null
)

select obp.*,
       
	   obp_prev.pk, obp_prev.hrchID,
	   obp_next.pk, obp_next.hrchID,
	   
	   case
	    when obp.hrchID between coalesce(obp_prev.hrchID, obp.hrchID) and coalesce(obp_next.hrchID, obp.hrchID)
		then 1
	    else 0
	   end isCorretValue,

	   cast(obp_prev.hrchID as nvarchar) as prev_hrchID,
	   cast(obp.hrchID as nvarchar) as hrchID,
	   cast(obp_next.hrchID as nvarchar) as next_hrchID,

	   cast(obp.hrchID.GetAncestor(1)  as nvarchar)
	   	    
  from cteOrderedByParents obp

 inner
  join cteInsertedTr iTr
    on obp.pk = iTr.pk
  
  left
  join cteOrderedByParents obp_prev
    on (obp_prev.fkOwner = obp.fkOwner or obp_prev.fkOwner is null and obp.fkOwner is null)
   and obp_prev.rowNum = obp.rowNum - 1
   and obp.rowNum <> 1
  
  left
  join cteOrderedByParents obp_next
    on (obp_next.fkOwner = obp.fkOwner or obp_next.fkOwner is null and obp.fkOwner is null)
   and obp_next.rowNum = obp.rowNum + 1

order by obp.hrchID


т. е. по факту проверяем порядок который должне быть и который есть на самом деле и "там ли где надо находится".
Может быть подзапросы с Top1 более быстродействующие тут будут для определения "текущей и следующей записи". Хотя думаю тут уже зависит от теста. Да, упорядочивать когда много записей на одном уровне при фактическом изменении одной записи - накладно. Будем думать (наверное тоже потом).

После такого результата так же бы хотелось обработать кейсы в духе если одновременно попадает и родительская и дочерняя запись, причём дочерняя сама по себе не меняет свего места (ну было "Name Bla", а стало "Name Blaaaa") а вот из-за изменения родительской и дочерняя "попадает под раздачу" новых значени иерархического поля.
Всё же лучше когда в СУБД иерархия организована не более красивым аналогом like 'ParentID%' (правда хорошо работающим вверх и вниз.
15 ноя 13, 15:54    [15137389]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Вот в таком примере можно выкинуть дочерние записи если у них не правильный порядок, но при этом родитель тоже имеет неправильный порядок и у него всё равно будет изменён hrchID, что повлечёт за собой изменение дочернего (хотя такая схема такая будет работать если полностью перестраивается дочерняя ветка, а не заменяется кусок у hrchID дочерних записей).

Этот вариант не решает проблему когда вставлены две новые записи которые иду друг за другом.

Кароче, "взрыв мозга" :)

with 

cteInsertedTr as
(
select * 
  from dbo.tCommonDictionary 
 where pk 
  in (2108, 2111, 5138, 4, 5137, 5136, 2844)
  --in ( 5138,  5137, 2111, 2108)
)
,

cteChangedParents as
(
select distinct
       fkOwner
  from cteInsertedTr
)
,

cteOrderedByParents as
(
select row_number() over (partition by chP.fkOwner order by v.Vendor_name, pk) as rowNum,
       v.* 
  from cteChangedParents chP
 inner
  join dbo.tCommonDictionary v
    on chP.fkOwner = v.fkOwner
	   or chP.fkOwner is null and v.fkOwner is null
)
,

cteCheckedOrder as
(
select obp.*,
       
--	   obp_prev.pk, obp_prev.hrchID,
--	   obp_next.pk, obp_next.hrchID,
	   
	   case
	    when obp.hrchID between coalesce(obp_prev.hrchID, obp.hrchID) and coalesce(obp_next.hrchID, obp.hrchID)
	    and obp.pk not in (4, 5136, 5138) --for testing
		then 1
	    else 0
	   end isCorretValue,

	   cast(obp_prev.hrchID as nvarchar) as prev_hrchID,
	   cast(obp.hrchID as nvarchar) as strhrchID,
	   cast(obp_next.hrchID as nvarchar) as next_hrchID--,

--	   cast(obp.hrchID.GetAncestor(1)  as nvarchar)
	   	    
  from cteOrderedByParents obp

 inner
  join cteInsertedTr iTr
    on obp.pk = iTr.pk
  
  --these left join may be not the best solution because some parent row may have big amount of child rows 
  --and the same CTE may be called few times
  left
  join cteOrderedByParents obp_prev
    on (obp_prev.fkOwner = obp.fkOwner or obp_prev.fkOwner is null and obp.fkOwner is null)
   and obp_prev.rowNum = obp.rowNum - 1
   and obp.rowNum <> 1
  
  left
  join cteOrderedByParents obp_next
    on (obp_next.fkOwner = obp.fkOwner or obp_next.fkOwner is null and obp.fkOwner is null)
   and obp_next.rowNum = obp.rowNum + 1
)

select chOr.*
  from cteCheckedOrder chOr
  
  /*We need to exclude child rows event they have isCorrectValue = 0 because we prevent double update with unpredictable order */
  left
  join cteCheckedOrder chOrExcl
    on chOr.hrchID.GetAncestor(1).IsDescendantOf(chOrExcl.hrchID) = 1 
   and chOrExcl.isCorretValue = 0
  -- and chOrExcl.hrchID <> chOr.hrchID 
 
 where chOr.isCorretValue = 0
   and chOrExcl.hrchID is null
    
 
order by chOr.hrchID
15 ноя 13, 16:33    [15137815]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
NIIIK
Я в принципе планирую даже не instead of, а обычный after insert/update
Mnior
К примеру если у вас HierarchyID уникальное поле, то как вы будете прозрачно вставлять данные?
Mnior
AFTER не подходит для уникальности поля HierarchyID, от которой я бы не советовал отказываться.
Уникальный фильтрованный индекс? (для мусора)
Нет контроля родителя HierarchyID?
NIIIK
Предыдущее значение иерархического поля можно подумать "использовать ли для выборки что бы переапдейтить".
А стоит ли овчинка выделки?
Проблема в том, что у узла может меняться несколько родителей. И получить в один присест эту информацию нельзя, без рекурсии.

cteChangedParents, cteOrderedByParents - может использовать просто Exists?!
cteChangedParents - Вообще-то тут путается, там новые значения, а надо старые.
NIIIK
chP.fkOwner is null and v.fkOwner is null
Может стоит завести корневой элемент? Видно что скорее нет контроля родителя для HierarchyID, или надо писать NullIf(HID.GetAncestor(1),HierarchyID::GetRoot()).

between HierarchyID - не понимаю как это работает. Там же старые значения, притом в перемешку. А главное что это даёт?

Если на клиенте происходило много раз изменения, то порядок изменения важен (для иерархии), его нужно повторить. Более того, на клиенте как раз лучше работать с HierarchyID, ибо даже если компонент пользуется системой через родителей, внутри всё равно идёт иерархия.
15 ноя 13, 16:57    [15138008]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Mnior,

Mnior
Уникальный фильтрованный индекс? (для мусора)


Я скорее пока не замоачиваюст на Инстед ОФ и индексе. Больше сосредоточен на логической части.
Хотя задать какие-нить фейковые и завдомо уникальные значения, которые потом переапдейтятся думаю вариант.

Mnior
Нет контроля родителя HierarchyID?

Немного не понял?
Констрейнта нет на родителя, а вот значение должно быть правильным (после обработки триггером).


Mnior
А стоит ли овчинка выделки?
Проблема в том, что у узла может меняться несколько родителей. И получить в один присест эту информацию нельзя, без рекурсии.


Если бы я сам этого не знал - легче бы жилось :)
Пока я не то что "без рекурсии" (В духе ЦТЕ) я без "построчного вызова с сохранением во временную таблицу или табличную переменную" задачу не могу решить :)

Mnior
cteChangedParents, cteOrderedByParents - может использовать просто Exists?!

То мои бзики, будем надеятся что так "один раз вызывается" :)
Этот момент пока на саму задачу не влияет, а там когда уже "допиливать и планы смореть" - может и экзист используем, а так руки сами по дефолту рисуют.


Mnior
cteChangedParents - Вообще-то тут путается, там новые значения, а надо старые.

Не понял? нам вроде как надо в новую иерархию впихуть с новыми родителями.


Mnior
Может стоит завести корневой элемент? Видно что скорее нет контроля родителя для HierarchyID, или надо писать NullIf(HID.GetAncestor(1),HierarchyID::GetRoot()).

Ну идею рассматривал, но тут проблема в том что в существующий код надо впихивать и этот ОР с нулами раньше писать не приходилось, а фейковую запись для всех заводить не хотелось. Хотя да, такой ОР подгажевает запрос и его план. Но на основуню проблему задачи пока не влияет.

Mnior
between HierarchyID - не понимаю как это работает. Там же старые значения, притом в перемешку. А главное что это даёт?

Это пока кривая проверка на то "находится ли на своём месте между двумя другими дочерними записями". Что-то вроде обратной/булевой GetDescendant(v1, v2).Грубо говоря что бы знать надо ли менять или должна остаться на своём месте.

Mnior
Если на клиенте происходило много раз изменения, то порядок изменения важен (для иерархии), его нужно повторить. Более того, на клиенте как раз лучше работать с HierarchyID, ибо даже если компонент пользуется системой через родителей, внутри всё равно идёт иерархия.

Ну тут и я предпочитаю "На сторне базы" и целом "уже есть" и фиг его поймёшь где клиента трогать.
В идеале я хотел бы "супе-триггер (копи-паскуод) в который передаёшь где тут ПК, где ссылка на родителя и он "всё сам делает"".
Но ещё не нашёл :).

Слишком "мануальный" у МС этот иерархический тип. с Ораклом 100 лет не работал, а connect by prior до сих пор забыть не могу :(
15 ноя 13, 22:06    [15139551]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID триггер на существующую таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
NIIIK
Хотя задать какие-нить фейковые и завдомо уникальные значения, которые потом переапдейтятся думаю вариант.
Я вам сразу ответ пишу, а вы его в упор не видите.
Неизвестные значения не генерятся уникальными а тупо NULL вставляется. А уникальность задаётся уникальным индексом отфильтрованном по не NULL значениям. Вставляйте хоть 100500 строк - просто и прозрачно.
NIIIK
Констрейнта нет на родителя, а вот значение должно быть правильным (после обработки триггером).
Ваше дело. Чем сложнее система в неумелых руках (те кто поддерживает) тем больше вероятность её свалить, если в ней мало контроля.
К примеру у родителей есть проблема зацикливания, иерархия не имеет такой проблемы. В итоге вы строите надёжность на не надёжном механизме.
NIIIK
... задачу не могу решить :)
Ну тогда зачем ломать голову над сложным вариантом, сделайте хотя бы как я предложил.
NIIIK
То мои бзики, будем надеятся что так "один раз вызывается" :)
Этот момент пока на саму задачу не влияет, а там когда уже "допиливать и планы смореть" - может и экзист используем, а так руки сами по дефолту рисуют.
Ну так это же проще, меньше писать и легче воспринимается. Так и леса за деревьями можно не увидить.
Заодно накачаете соображалку вариантов записи.
NIIIK
Не понял? нам вроде как надо в новую иерархию впихуть с новыми родителями.
Ок, фиг со старыми ветками, пусть будут дырявыми. Но там старые данные HID, которые явно уже не верны, на новых местах.
NIIIK
Хотя да, такой ОР подгажевает запрос и его план. Но на основуню проблему задачи пока не влияет.
Влияет на соображалку. Меньше элементов, больше оперативного места в гипокампе для решения задачи.
На план кстати не влияет.
NIIIK
Грубо говоря что бы знать надо ли менять или должна остаться на своём месте.
Но как это можно решать на основе соседей которые были также вставлены совершенно из разных мест?! У вас получатся островки которые якобы не надо менять, хотя иерархия в них левая.
NIIIK
Ну тут и я предпочитаю "На сторне базы" и целом "уже есть" и фиг его поймёшь где клиента трогать.
Не понимаю. Для чего вам эта иерархия? Допустим для частых запросов, иначе в топку её.
А как будут они себя вести когда вы поменяете 0.1% дерева у корня, которое влияет на 90% всего дерева? Пока вы будете восстанавливать триггером иерархию, остальные запросы будут делать своё уже грязное дело, не говоря о дедлоках.
Читаете иерархию - с неё и изменяйте, или у вас там режим версионности? (это говно портит людей)
NIIIK
Слишком "мануальный" у МС этот иерархический тип.
Чтобы где-то добавилось, надо чтобы где-то убавилось. Как грится вам шашечки или ехать. И повторю - в месте где эта иерархия меняется как раз она полезна и лучше работает.
В итоге на клиенте делается извращение - пересчитывается иерархия на основе родителей и обратно, а потом на сервере это повторяется, только в слепую.
Не знаю на чём клиент, но даже в старых системах было оба варианта представления.
NIIIK
с Ораклом 100 лет не работал, а connect by prior до сих пор забыть не могу :(
А, вот кто вас "испортил".
И connect by prior - это тот же недоделанный рекурсивный CTE.

Найду время, попробую подключится.
16 ноя 13, 05:18    [15140674]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить