Блог

Почему следующая ошибка (не удается обновить таблицу ‘table_b’ в сохраненной функции / триггере, потому что она уже используется оператором, который вызвал эту сохраненную функцию / триггер) появляется после того, как я пытаюсь удалить с помощью inner join? Могу ли я это решить?

 DROP TABLE if exists table_b;
DROP TABLE if exists table_a;

CREATE TABLE table_a (
  id int auto_increment,
  name varchar(255) DEFAULT NULL,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE if exists table_b;
CREATE TABLE table_b (
  id int auto_increment,
  name varchar(255) DEFAULT NULL,
  id_table_a int NOT null,
  another_table_id int NOT null,
  foreign key (id_table_a) references table_a(id),
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DELIMITER $$
drop trigger if exists tg_test$$
create trigger tg_test before delete on table_a for each row
begin
  delete from table_b where id_table_a = OLD.id;
end$$

DELIMITER ;

insert into table_a(name) values('t-a');
insert into table_b(name, id_table_a, another_table_id) values('t-b', 1, 23);

-- Error Can't update table 'table_b' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
-- in any of this two lines
delete table_a from table_a inner join table_b on table_b.id_table_a = table_a.id where another_table_id = 23;
delete from table_a where id in (select id_table_a from table_b where another_table_id = 23);

-- Success
delete from table_a where id = 1;
  

Я не вижу смысла в триггере здесь. Требуемая функциональность может быть достигнута простым добавлением on delete cascade опции к объявлению вашего внешнего ключа:

 CREATE TABLE table_b (
  id int auto_increment,
  name varchar(255) DEFAULT NULL,
  id_table_a int NOT null,
  another_table_id int NOT null,
  foreign key (id_table_a) references table_a(id) on delete cascade,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  

Теперь всякий раз, когда запись удаляется из table_a , дочерняя запись (как указано в определении внешнего ключа) удаляется из table_b .

При такой настройке (и без триггера) оба ваших delete оператора будут выполняться нормально. На самом деле я бы использовал exists вместо join или in , но это в основном дело вкуса:

 delete from table_a 
where exists( 
    select 1 
    from table_b b 
    where b.id_table_a = table_a.id and b.another_table_id = 23
);