Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > Триггер и рекурсивная процедура

Автор: Shogun 9.12.2010, 19:16

Добрый вечер всем!

Есть таблица items:
item_id | parent_id | progress
-----------------------------------
1 0 0

Создаю следующий триггер к этой таблице для вставки:

Код


delimiter |
create trigger up_progress after insert on items
  for each row begin
    if (new.parent_id > 0) then
      call update_item_progress(new.parent_id);
    end if;
  end;
|
delimiter ;

Код


delimiter |
create procedure update_item_progress(in p_id int)
begin
  declare total_items int default 0;
  declare total_items_progress int default 0;
  declare t_progress int default 0;

  set total_items = (select count(*) from items where parent_id=p_id);
  set total_items_progress = (select sum(progress) from items where parent_id=p_id);
  
  set t_progress = round((total_items_progress / (total_items * 100)) * 100);

  update items set progress=t_progress where item_id=p_id;

  set p_id = (select parent_id from items where item_id=p_id);
  
  if (p_id > 0) then 
    begin
      call update_item_progress(p_id);
    end;
  end if;
end;
|
delimiter ;

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

Собственно при вставке:

Код


insert into `items` (`item_id`, `parent_id`, `progress`) values ('4', '1', '100'), ('5', '1', '0');

то в результате, прогресс записи с ид 1 должен стать 50.

триггер добавился, процедура тоже, но при добавления этих новых записей прогресс у родителя не обновляется ((

помогите пожалуйста кто знает как решить или что я не правильно сделал.

пс : это мои первые опыты с триггерами и процедурами, так что просьба ногами не бить... =))

версия mysql 5.1.40

немного разобрался, теперь триггер вызывается и процедура тоже, но при вставке выходит ошибка:
#1442 - Can't update table 'items' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

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

Помогите пожалуйста... ((

Спасибо! ;)

Автор: Shogun 11.12.2010, 00:19

Знает ли кто что я не правильно тут сделал?? можете помочь? smile

Автор: Zloxa 11.12.2010, 12:45

Цитата(Shogun @ 9.12.2010, 19:16

)

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

В оракле это называют мутацией.
В оракле, в подобных случаях, в for each row триггерах откладывают измененные записи во временную таблицу или коллекцию, а затем, в after statement триггере выполняют необходимые операции.

Добавлено @ 12:47
чесгря удивлен, что и в маське есть эта проблема.
Я думал это чисто ораклиная проблема и связанна она с реализацией версионности. А маська, как известно - блокировочник.

Автор: Shogun 11.12.2010, 13:42

Zloxa, хмм а я то думал в оракле и других серьозных бд этой проблемы нет...

Если кто поможет кодом для моей задачей буду очень благодарен.

А может есть более традиционные подходы к решению такой проблемы??

Автор: Shogun 14.12.2010, 19:17

Попробовал с помощью вью.

Код


create view items_v as select item_id, parent_id, progress from items

и в процедуре update_item_progress заменил название таблицы на название вью.

Но это тоже не прошло (( таже ошибка 1442

Может кто помочь? Можно ли ваше с помощью триггера и процедуры решить такую задачу или придется по прежнему все это делать через "одно место"??

Автор: azesmcar 14.12.2010, 20:55

http://forums.mysql.com/read.php?99,122354,240978#msg-240978

вроде бы нету пока решения.

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

Цитата(Shogun @ 14.12.2010, 19:17

)

Может кто помочь? Можно ли ваше с помощью триггера и процедуры решить такую задачу или придется по прежнему все это делать через "одно место"??

Можно обновлять не через запрос а через процедуры, из нее же вызывать update_item_progress а триггер убрать.

Автор: Shogun 14.12.2010, 21:16

azesmcar, ясно, жаль...

Цитата

Можно обновлять не через запрос а через процедуры, из нее же вызывать update_item_progress а триггер убрать.

ну да, update_item_progress и есть процедура через которую обновляю рекурсивно, а триггер просто нужен был для того чтоб автоматически после вставки вызвать эту процедуру, а так придется это делать вручную.

Автор: azesmcar 14.12.2010, 21:35

Цитата(Shogun @ 14.12.2010, 21:16

)

неправильно выразился, имелось ввиду вставлять запись с помощью процедуры, из нее же вызывать update_item_progress. Т.е. вместо пары insert и trigger иметь одну процедуру, которая будет делать вставку и функцию, которая была возложена на триггер.

Автор: Shogun 14.12.2010, 22:21

Цитата

ааа, неплохое решение вроде, спасибо попробую, но тут могут быть и неудобства при таблицах с многими полями как мне кажется, плюс есть еще риск если запись не добавится, или можно это в процедуре проверить?

а на производительность это сильно отразится?

Спасибо.

Автор: azesmcar 15.12.2010, 05:42

Цитата(Shogun @ 14.12.2010, 22:21

)

но тут могут быть и неудобства при таблицах с многими полями как мне кажется

не думаю, запрос на insert со многими полями будет смотреться в коде хуже чем вызов процедуры. Запросу должны быть переданы как значения, так и сами поля, а процедуре можно не передавать.

Цитата(Shogun @ 14.12.2010, 22:21

)

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

конечно, это может быть и функция, возвращающая код ошибки, можно еще посмотреть есть ли в mysql исключения.

Цитата(Shogun @ 14.12.2010, 22:21

)

а на производительность это сильно отразится?