Есть таблица prspc_content которая содержит иерархические данные:

ID - идентификатор
FATHER - родитель
COUNT - количество
MULTI – множитель

Пытаюсь написать триггер BEFOR UPDATE для этой таблицы, суть которого – при изменении поля COUNT – пересчитать поле MULTI для всех подчиненных из этой же таблицы, например:

ID      FATHER  COUNT   MULTI
1         0           10          1            
2         1           5            0
3         1           4            0

Изменяя запись с ID=1 (COUNT=10) на COUNT=5 пересчитать значения поля MULTI для записей с ID=2 и ID=3. Как правильно это сделать, что бы не вылетала ошибка «table is mutating»? Задача, казалось бы, классическая, но за отсутствием опыта я в тупике.

Вот как делаю я:



А как правильно?

а поясните, пожалуйста, суть полей COUNT и MULTI
Обычно, мутация таблиц - следствие неверного проектирования

COUNT – количество деталей в единице изделия
MULTI – множитель (с учетом входимости)

Но это не имеет принципиального значения. Я так понимаю, проблема здесь в том, что я пытаюсь изменить ту же самую таблицу, для которой сработал триггер. Поэтому триггер вызывается рекурсивно, а это, судя по всему, БД переварить не может. Сейчас этот алгоритм реализован программно, т.е. в exe-шнике – все работает – просто хочется немного оптимизировать задачу и часть нагрузки повесить на сервак…


Это сообщение отредактировал(а) Matrex - 22.6.2011, 16:59

Подход к БД не правильный - при изменении записи менять другие. Значит схема не нормализована.
может быть поле возможно MULTI высчитывать при запросе?
Можно отдельную вьюху завести если уже много кода завязано (или вирт. столбец в 11-м оракле)

Если все же решите остановиться на своем варианте - можно использовать планировщик Oracle.
В триггере создаете задание, которое  будет выполнено 1 раз. Если используете 10+ Oracle - используется пакет DBMS_SHEDULER, если 9- - DBMS_JOB (хотя его можно и в более поздних версиях использовать).
Но тут есть подводный камень - даже если указать, что задание необходимо выполнить немедленно - все равно будет задержка.

Это сообщение отредактировал(а) turbanoff - 23.6.2011, 06:56

есть еще общий поход:
придется создать 3 триггера:
1. before общий (не for each row)
2. before for each row
3. after общий

создается пакет с переменной - pl/sql таблицей.

в 1-м триггере - переменная (таблица) очищается
во 2-м - в таблицу записывается id записей для которых необходимо выполнить пересчет
в 3-м (after) выполняется пересчет таблицы. по тем ID которые запомнены в переменной пакета.


PS. Если это единственный триггер - никакой рекурсии вызова триггера не происходит: триггер на апдейт prspc_content , а в триггере меняется другое поле - multi. 
Мутация  - выборка или изменение таблицы A в триггере for each row. при этом триггер сработал в результате изменения таблицы A.

В книге Oracle PL/SQL для профессионалов. Практические решения - Коннор МакДональд есть глава посвященная мутации таблиц.

Это сообщение отредактировал(а) turbanoff - 23.6.2011, 07:21

Ну не знаю… Может быть, и найдется другое решение – буду рад услышать. Тогда попробую объяснить, зачем это нужно.

Собственно подобной таблицей (prspc_content) описывается состав изделия:

ID      FATHER  COUNT   MULTI   ID_DSE                  ID_PRSPC
1         0               1      1         100 (Стол)              200   (Тумба)
2         1               5      1         101 (Винт)              200   (Тумба)
3         1               4      1         102 (Шайба)           200   (Тумба)

т.е. есть изделие «Тумба» которое состоит из одного «Стола» (COUNT=1), который в свою очередь состоит из 5 «Винтов» и 4 «Шайб». Задача – посчитать количество деталей на изготовление одной тумбы. Тут все просто. Можно посчитать хоть на сто тумб. Теперь изменим ситуацию – представим, что тумба состоит из двух столов (количество «Столов» COUNT = 2, при этом количество «Винтов» и «Шайб» не меняем /требование заказчика/) тогда получается так – что на изготовление одной тумбы требуется 2 стола 10 винтов и 8 шайб. 

Для того, что бы правильно рассчитать количество деталей, необходим предварительный расчет некоего «множителя». Раньше он рассчитывался динамически во время выполнения запроса – просто вызывалась функция для каждого идентификатора детали. Но когда составы изделий выросли до 200-300 единиц, а номенклатура до 40-50 наименований, да плюс неограниченное количество «вложенности» – возникли проблемы с производительностью.



При добавлении записей это именно так и делается. А вот что делать если запись меняется. См. пример выше…



Ясно буду пробовать. Спасибо…

насколько я понял:
MULTI - это собственно количество деталей в изделии.
а count - это не количество деталей, а кол-во наборов поддеталей.

правильно?

Теперь осталось понять когда необходимо пересчитывать multi, и зачем?

Нет. Это множитель для пересчета фактического количества деталей с учетом входимости


Нет. Это количество деталей в изделии


Например. Вот две спецификации:

НАИМЕНОВАНИЕ ДЕТАЛИ    КОЛИЧЕСТВО (COUNT)
Тумба                                1
  Стол                                1  < тумба состоит из одного стола
      Винт                            5  < для изготовления одного стола требуется 5 винтов
      Шайба                         4  < и 4 шайбы
Всего деталей для изготовления такой тумбы: 1 стол; 5 винтов, 4 шайбы.

и

НАИМЕНОВАНИЕ ДЕТАЛИ    КОЛИЧЕСТВО (COUNT)
Тумба                                1
  Стол                                2  <<<<<< тумба состоит из ДВУХ столов
      Винт                            5  < для изготовления ОДНОГО стола требуется 5 винтов
      Шайба                         4  < и 4 шайбы
Всего деталей для изготовления такой тумбы:  2 стола; 10 винтов, 8 шайб.

При составлении спецификаций количество деталей указывается на 1 сборочную единицу см. количество «Столов» (2 шт) и количество «Винтов» и «Шайб» (указано на изготовление одного стола).



Пересчет должен выполняться при добавлении новой записи и при изменении количества деталей родительского элемента для всех его подчиненных. А зачем? Что бы корректно посчитать количество деталей необходимых для изготовления изделия (расчет выполняется путем перемножения COUNT*MULTI).

Собственно говоря, это все. По другому как это сделать я не знаю. Если есть соображения – я бы послушал…

ааа, вот оно как. то есть multi введено только ради производительности?.
А можно узнать сколько строк в таблице? хотя бы порядок. Oracle может "прожевывать" довольно большие таблицы быстро, если есть индексы.

Это сообщение отредактировал(а) turbanoff - 23.6.2011, 13:27

давно уже ридонли, но иногда нервы не выдерживают.

pragma autonomous_transaction для триггера.

Да.



40 изделий x 300 деталей т.е. обрабатывается около 12000 записей для расчета необходимого количества деталей. Если для каждой из записи во время selecta рассчитывать множитель по функции – по времени занимает около 3х минут. И индексы тут не помогают т.к. для каждой из 12000 записей необходимо выполнить иерархический select для расчета множителя, а вот если множитель рассчитан заранее - около 1 сек…


А чего так?

 
Понял – буду читать матчасть. Спасибо.

Дык он текущих изменений не увидит, как же ему пересчитывать данные?





Подводный камень как раз в обратном, JOB может сработать раньше, чем закончится транзакция и JOB просто не увидит изменений 

Добавлено через 5 минут и 22 секунды
"По правилам" в таблицах не стоит хранить данные, которые можно вычислить на основе уже имеющихся данных.

Мне кажется стоит сделать или view, или materialized view если будут проблемы с производительностью.

Если сделать вьюшку, то все равно придется вызывать функцию расчета множителя – а это сведет на нет весь эффект от повышения производительности (у меня так и было в начале, другое дело что может функцию расчета множителя как то оптимизировать…). В данном конкретном случае метод «по правилам» не очень подходит. ИМХО, проще написать два триггера на добавление/изменение записи... А тут такая засада... На добавление триггер работает – тут все ок, а вот с изменением…

Это сообщение отредактировал(а) Matrex - 23.6.2011, 14:52

LSD, какие текущие изменения нужны?


Добавлено через 4 минуты и 2 секунды

опять... ты что мат часть изучил уже?

Добавлено через 9 минут и 25 секунд

время жизни постов обратно пропорционально моему свободному времени, вот как сейчас, сказал слово и началось   

Это сообщение отредактировал(а) DimW - 23.6.2011, 15:26

Всем спасибо, особый респект DimW вот решение: