I have a table where stock quantity is recorded. I set trigger to warn the user when stock goes low. When testing it, the trigger fires, displays the message, but the stock is not updated. What do I do wrong? Is there another way to set the trigger (not using SIGNAL SQLSTATE)? I use phpMyAdmin 18.104.22.168 in uWamp. Thank you for suggestions.
Code for the trigger:
DELIMITER $$ CREATE TRIGGER low_stock_message AFTER update ON stock_quantity FOR EACH ROW BEGIN IF (NEW.Regular_Menu < 50) THEN SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning, Regular Menu has a low stock!'; END IF; IF (NEW.Savers < 50) THEN SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning, Savers Menu has a low stock'; END IF; IF (NEW.Breakfast < 50) THEN SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning, Breakfast Menu has a low stock!'; END IF; END$$ DELIMITER ;
UPDATE stock_quantity SET Regular_Menu=48 WHERE Outlet_ID=1;
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `stock_quantity` ADD INDEX `stock_quantity_idx_outlet_id` (`Outlet_ID`);
SELECT stock_quantity.Regular_Menu FROM stock_quantity WHERE stock_quantity.Outlet_ID = 1