MySQL trigger: ERROR #1442

Question

I am working on MySQL database and facing problem creating trigger. In the database, suppose there are two tables, "user" and "book" with 1 to many relationship and user_id is foreign key in "book" table. I have created a trigger which decreases the "book_count" column in "user" table by 1 when a book is deleted.

CREATE TRIGGER `after_book_delete` AFTER DELETE ON `book`
FOR EACH ROW BEGIN
UPDATE `user` SET `user`.book_count = `user`.book_count - 1 WHERE `user`.user_id = OLD.user_id;
END;

Now I want to delete all books of a user when the user is deleted. I tried this:

CREATE TRIGGER `before_user_delete` BEFORE DELETE ON `user`
FOR EACH ROW BEGIN
DELETE FROM `book` WHERE user_id = OLD.user_id;
END;

This is returning the following error:

#1442 - Can't update table 'user' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I can understand that the error is for the first trigger where it tries to update a row in user table which is about to delete. So is there any workaround if I want to do it by trigger?

Answer 1

Declare your foreign key constraint to user in the book table with

 ON DELETE CASCADE ON UPDATE CASCADE

For example,

 CONSTRAINT fk_userbook FOREIGN KEY (id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE

Then if you delete a user, all related books will be deleted as well, negating the necessity of a trigger.

Extended example,

create table user (
     id int,
     constraint pk_user primary key(id)
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

create table book (
     id int,
     uid int,
     bname varchar(10),
     constraint pk_book primary key(id),
     constraint fk_userbook foreign key (uid) references user (id) on update cascade on delete cascade
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

insert into user values(1);
insert into book values(1,1,"a");
insert into book values(2,1,"b");
insert into book values(3,1,"c");
insert into book values(4,1,"d");

delete from user where user.id = 1;
select * from book;

As you can see, this automatically takes care of what your trigger is trying to achieve.