Error 1442: MySQL Error In phpMyAdmin related to triggers

Question

I created this Trigger on a books table

CREATE TRIGGER tBookCopies AFTER INSERT ON books 
FOR EACH ROW 
BEGIN 
UPDATE books 
SET availableCopies=books.totalCopies 
WHERE books.book_ID = NEW.book_ID;
END

The purpose of the trigger was to automatically set the values of the available copies to be the same as total copies after an insert is done. When I tried an insert query on the table

MySQL said:

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

please how can this issue be solved?

Answer 1

you can not update the sama table but changing it to a before INSERT trigger does the same trick

CREATE TRIGGER tBookCopies BEFORE INSERT ON books 
FOR EACH ROW 
BEGIN 

   SET NEW.availableCopies=NEW.totalCopies; 

END