Why the following error (Can’t update table ‘table_b’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger) appears after i try to delete with inner join? Can i solve it?
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;
Advertisement
Answer
I don’t see the point for a trigger here. The functionality you want can be achieved by just adding the on delete cascade
option to the declaration of your foreign key:
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;
Now anytime a record is dropped from table_a
, the children record (as designated by the foreign key definition) are deleted from table_b
.
With this set-up (and without the trigger), both your delete
statements would run fine. I would actually use exists
rather than a join
or in
, but that’s mostly a matter of taste:
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 );