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 4.2.7.1 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 ;
Test query:
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