Пытаюсь обновить данных в выборке, но при удалении получаю следующую ошибку:
Error Code: 1442. Can't update table 'helpdesk' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Тригер выглядит следующим образом:
P.S> Использовал итерацию по курсору так как думал, что это может решить мою проблему
DROP TRIGGER IF EXISTS helpdesk_before_delete //
CREATE TRIGGER `helpdesk_before_delete`
BEFORE DELETE ON `helpdesk`
FOR EACH ROW BEGIN
DECLARE done INT DEFAULT 0; -- flag for cycle
DECLARE current_id_in_cycle INT;
DECLARE current_order_in_cycle INT;
DECLARE lower_data_selection CURSOR FOR SELECT h.id, h.order from helpdesk AS h WHERE h.order > OLD.order AND h.parent = OLD.parent; -- for updating elements in old list
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN lower_data_selection;
WHILE done =0 DO
FETCH lower_data_selection_by_old_parent INTO current_id_in_cycle, current_order_in_cycle;
UPDATE helpdesk AS h SET h.order = current_order_in_cycle-1 WHERE h.id= current_id_in_cycle;
END while;
SET done=0;
CLOSE lower_data_selection;
END //
Простой
