Почему выдает ошибку 1442?

Question

[Galdar Turin]

Обычно такую ошибку я видел если ставить триггер на UPDATE и делать UPDATE таблицы в триггере, тогда да, ошибка на бесконечный цикл. Но тут почему? Триггер стоит на INSERT, если добавление данных и в полях NULL то заполнять поля из других таблиц. Подскажите что я делаю не так?

Can't update table 'employees' in stored function/trigger because it is already used by statement which invoked this stored function/triggeruery insert

SQL

CREATE DEFINER=`root`@`localhost` TRIGGER `trigger_in_employees` AFTER INSERT ON `employees` FOR EACH ROW BEGIN

	IF NEW.entpr_name IS NULL THEN 
		UPDATE `employees` SET `entpr_name` = (SELECT `ap_name` FROM `companies` WHERE `id` = NEW.`entpr_id`) WHERE `id` = NEW.`id`;
	END IF;
	
	IF NEW.employee_name IS NULL THEN 
		UPDATE `employees` SET `employee_name` = (SELECT CONCAT(`np_surname`,' ',`np_name`,' ',`np_patronymic`) FROM `people` WHERE `id` = NEW.employee_id) WHERE `id` = NEW.`entpr_id`;
	END IF;

END

Answer 1

[Akina]

CREATE DEFINER=root@localhost
TRIGGER bi_trigger_in_employees
BEFORE INSERT ON employees 
FOR EACH ROW 
SET NEW.entpr_name = COALESCE( NEW.entpr_name, 
                               ( SELECT ap_name 
                                 FROM companies 
                                 WHERE id = NEW.entpr_id 
                                 LIMIT 1
                                )
                              ),
    NEW.employee_name = COALESCE( NEW.employee_name, 
                                  ( SELECT CONCAT_WS(' ',np_surname,np_name,np_patronymic) 
                                    FROM people 
                                    WHERE id = NEW.employee_id
                                    LIMIT 1
                                   )
                                 );

1) Суть ошибки - именно та, что написана в сообщении. Нельзя UPDATE таблицы, на которой определён триггер (и другие DML - тоже нельзя).
2) Изменять вставляемые значения надо до вставки, а не после. Потому BEFORE триггер.
3) По-хорошему, нужен ещё аналогичный BEFORE UPDATE триггер.
4) Все SET выполняются в одном операторе (см. https://dev.mysql.com/doc/refman/8.0/en/set-variab...). А триггер из одного оператора обходится и без BEGIN-END, и без DELIMITER.
5) При скалярном присвоении из подзапроса ВСЕГДА добавляйте LIMIT 1. А по-хорошему ещё и ORDER BY. На показанный код ни то, ни другое не влияет - всё равно одна запись, ибо отбор по PK. Но вдруг структура поменяется, а про триггер забудете? Начнутся ошибки - оно надо?

тогда да, ошибка на бесконечный цикл. Но тут почему?

Как это ни странно, но в теле триггера неизвестно, на какое событие определён триггер.

Comments

[Galdar Turin]

Akina, огромное спасибо за подробное объяснение!

Answer 2

[Михаил]

Сделайте триггер BEFORE INSERT и задайте значение полям объекта NEW (как это и предполагается таким триггером by design)