Вопрос:
У меня есть таблица тестирования с атрибутом order. Когда order изменяет его значение, мне нужно пересчитать все значения order в таблице. У меня есть следующий триггер:
DELIMITER $$ USE `testing`$$ DROP TRIGGER /*!50032 IF EXISTS */ `UpdateOrder`$$ CREATE /*!50017 DEFINER = ‘root’@’localhost’ */ TRIGGER `UpdateOrder` AFTER UPDATE ON `upd_tst` FOR EACH ROW BEGIN IF (OLD.order > NEW.order) THEN UPDATE `upd_tst` t SET t.`order` = t.`order` + 1 WHERE t.`order` < OLD.order AND t.`order` >= NEW.order AND t.id <> OLD.id; ELSEIF (OLD.order < NEW.order) THEN UPDATE `upd_tst` t SET t.`order` = t.`order` — 1 WHERE t.`order` > OLD.order AND t.`order` <= NEW.order AND t.id <> OLD.id; END IF; END; $$ DELIMITER ;
Когда я пытаюсь обновить любой элемент, я получаю ошибку nex:
Executed SQL Statement : update `testing`.`upd_tst` set `order`=’3′ where `id`=’3′ Error Number : 1442 Error Message: Can’t update table ‘upd_tst’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Как я могу исправить эту проблему? Мне нужно использовать ТОЛЬКО триггер.
В postgresql я могу использовать это условие внутри триггера WHEN (pg_trigger_depth() = 0). В mysql есть что-то подобное?
Ответ №1
Решение. Сделайте триггер BEFORE UPDATE ON, чтобы вы могли установить/обновить значения полей в триггере SQL:
DELIMITER $$ USE `testing`$$ DROP TRIGGER /*!50032 IF EXISTS */ `UpdateOrder`$$ CREATE /*!50017 DEFINER = ‘root’@’localhost’ */ TRIGGER `UpdateOrder` BEFORE UPDATE ON `upd_tst` FOR EACH ROW BEGIN IF (OLD.order > NEW.order) THEN SET NEW.order = NEW.order` + 1; ELSEIF (OLD.order < NEW.order) THEN SET NEW.order = NEW.order — 1; END IF; END; $$ DELIMITER ;
Причина. Вы не можете обновлять/удалять/вставлять в таблицу, где вызывается триггер:
Внутри хранимой функции или триггера не разрешается изменять таблицу, которая уже используется (для чтения или записи) оператор, вызывающий функцию или триггер.
Это приведет к возникновению ошибки 1442:
Error Code: 1442 Can’t update table ‘MyTable’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
