Table Data and More Info here
products_stock has a foreign key relationship to products via the mpn column.
Basic Rundown: If an mpn has at least one row greater than 0 in the available column on my products_stock table, the column in_stock should be triggered to 1 in the products table to indicate stock.
If all of the mpn's row(/size variant) are set to 0 in the available column, the trigger should set in_stock on the products table to 0 to indicate no stock.
The former is working great to indicate there is stock, the latter is not working at all when available is at 0. I've deduced it down to it not being able to pass the IF rowCount < 1 THEN declaration. Here's the trigger as it is now:
BEGIN
DECLARE rowCount INTEGER;
IF NEW.available <> OLD.available THEN
IF NEW.available > 0 AND OLD.available <= 0 THEN
UPDATE
products
SET
products.in_stock = 1
WHERE
products.mpn = NEW.mpn;
ELSEIF NEW.available <= 0 AND OLD.available > 0 THEN
SET
@rowCount := (
SELECT
COUNT(NEW.mpn)
FROM
product_stock
WHERE
product_stock.available > 0
);
IF rowCount < 1 THEN
UPDATE
products
SET
products.in_stock = 0
WHERE
products.mpn = NEW.mpn;
END IF;
END IF;
END IF;
END
I've tried many different variants for setting @rowCount -- directly underneath DECLARE rowCount INTEGER;, embedded in the IF condition (as seen above), adding another AND condition in the declaration (product_stock.available > 0 AND product_stock.mpn = NEW.mpn);) and so forth.
The issue is rowCount is not putting into consideration the row's MPN when doing it's count -- and is instead counting all the rows, regardless of MPN, with product_stock.available > 0.
If I take out @rowCount, it acts as it should to trigger a 0 in products.in_stock, but does not put into consideration any other variants the mpn has.
Is it possible to modify the trigger/declared variable to only count the mpn that the available column is being UPDATED on?
