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
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.
You don’t need a query to get the status, just use
new.status in the
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 ;