MySQL error - Can't update table 'library_audit2' in stored function/trigger because it is already used by statement

Question

While deleting a record it is giving me an error. Where is the problem in the trigger.

Mysql code -

MariaDB [practice2]> delimiter $$
MariaDB [practice2]> create trigger BeforeLibraryDelete1
    -> BEFORE DELETE
    -> ON library_audit2 FOR EACH ROW
    -> BEGIN
    -> declare id1 int;
    -> select library_id into id1 from library_audit2 where change_date=OLD.change_date;
    -> delete from library_2 where library_2.id=id1;
    -> END $$
Query OK, 0 rows affected (0.128 sec)

MariaDB [practice2]> delimiter ;


MariaDB [practice2]> Delete from library_audit2 where change_date='2017-04-07';

On firing delete query, it gives error -

ERROR 1442 (HY000): Can't update table 'library_audit2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Answer 1

You have a delete trigger that fires for each row. I don't really see the point of selecting again from the source table just to get the id of the row being dropped. This information is available at trigger level already, in pseudo column old.id1.

Doesn't this do what you want?

create trigger beforelibrarydelete1
before delete on library_audit2 
for each row
begin
    delete from library_2 where id = old.id;
end $$

Reading the comment under the question, it seems like you are trying to handle foreign key dependencies with this trigger. If you have children records in library2 that reference library_audit2, then you can just use the on delete cascade option to automatically delete them when a record is dropped from the parent table.

create table library2 (
    ...
    foreign key (id)
        references library_audit2(id)
        on delete cascade
);