MySQL After Update TRIGGER with IF Conditional Statement

I need to create a trigger to update the data on another table when an update statement is made on the present table.

I have two tables, a book table, and a borrowed-book table. On the book table, I have an available copies column and on the borrowed-book table, I have a copy and book-status column.

So presently the trigger I am creating is meant to update the book table available-copies column by adding the values with the data on copies column after an update on the borrowed-book, if the book-status on the update is equal to ‘returned’.

what do I do:

BEGIN
DEFAULT @status TEXT
SELECT new.status as status from borrowed_book where borrowed_ID=new.borrowed_ID
  if status=="returned"
      UPDATE books b
      set 
       b.availableCopies=b.availableCopies + New.copies where b.book_ID=new.book_ID
END

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You don’t need a query to get the status, just use new.status in the IF statement.

DELIMITER $$

CREATE TRIGGER book_returned AFTER UPDATE ON borrowed_books
FOR EACH ROW
IF new.status = 'returned'
THEN
    UPDATE books b
    SET b.availableCopies=b.availableCopies + New.copies 
    WHERE b.book_ID=new.book_ID;
END IF;
$$

DELIMITER ;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0