MySQL After Update TRIGGER with IF Conditional Statement-CodePudding

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

CodePudding user response：

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 ;