After I defined a trigger and its procedure, to diminish the amount of books on stock by an amount specified on a separate table (both being variable), it gives me error 1054, of an unknown column in the procedure. The code that defines the trigger and procedure is the following:
CREATE DEFINER=`root`@`localhost` TRIGGER reduce_available
AFTER INSERT ON book_customer FOR EACH ROW
UPDATE stock SET stock.current_amount = stock.current_amount-book_customer.amount;
Usually to process the type of interactions that would warrant this type of trigger instead I generate an entirely new table that records the purchases and sellings, with aditional useful data. However, in this specific case I don't have such a table, and would like to know how to work around it.
Some clarification:
UPDATE stock SET stock.current_amount = stock.current_amount-1;
stock.current_amount
and book_customer.amount
) are the same type (INT).Do you just want to refer to new
? If so, I assume you only want to update one row in stock
rather than all of them. You need a where
clause
CREATE DEFINER=`root`@`localhost` TRIGGER reduce_available
AFTER INSERT ON book_customer FOR EACH ROW
BEGIN
UPDATE stock s
SET s.current_amount = s.current_amount - new.amount
WHERE s.book_id = new.book_id;
END;