Trigger Loop keeps putting currentStock on 0

Question

So I'm trying to work out a trigger that automatically adds the ingredients from a order (dhh_purchaseorder) to the table dhh_ingredients. It should get the new ordered amount out of dhh_purchaseorderingredient and dump it into the orderAmount variable. Then it should combine the current available stock with the ordered amount in newStock. Then it should update the ingredient table and set the new amounts for the correct ingredients but for some reason it keeps putting the currentStock column in ingredients to 0.

Here is my trigger:

BEGIN

DECLARE orderStatus, orderIngredientName VARCHAR(50);
DECLARE finished INTEGER DEFAULT 0;
DECLARE currentStock, newStock DECIMAL(10,2);
DECLARE orderNo, orderAmount int(10);
DECLARE lookupCursor CURSOR FOR SELECT INGREDIENTingredientName from dhh_purchaseorderingredient WHERE dhh_purchaseorderingredient.PURCHASEpurchaseOrderNo = NEW.purchaseOrderNo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

SET orderNo = NEW.purchaseOrderNo;
SET orderStatus = NEW.status;

IF(orderStatus = "DELIVERED") THEN

OPEN lookupCursor;

update_loop: LOOP
    FETCH lookupCursor INTO orderIngredientName;
    IF finished = 1 THEN
    LEAVE update_loop;
    END IF;

    SET orderAmount = (SELECT amount from dhh_purchaseorderingredient WHERE dhh_purchaseorderingredient.PURCHASEpurchaseOrderNo = orderNo AND dhh_purchaseorderingredient.INGREDIENTingredientName = orderIngredientName);
    SET currentStock = (SELECT currentStock FROM dhh_ingredient WHERE ingredientName = orderIngredientName);
    SET newStock = currentStock + orderAmount;

    INSERT INTO temp VALUES(currentStock);

    UPDATE dhh_ingredient
    SET currentStock = newStock
    WHERE ingredientName = ingredientName;

END LOOP update_loop;

CLOSE lookupCursor;

END IF;
END

score 0 · Answer 1 · edited May 23 '17 at 11:59

This is partly an answer and partly a suggestion for a better approach.

One of the many reasons I believe stored procedures are a very bad idea is that they are hard to debug. In this case, it's not easy to figure out what value is being assigned to the various stock amount variables.

A better approach is to use query that does update - without the need for SP variable. It's really easy to test it; just execute and inspect the results. By using such a query in your SP, you eliminate incorrect query logic from your code, allowing you to condense the SP to just the bits that matter - the update.

Fortunately, MySQL provides a way to update through a join, via its multi-table update syntax.

This is what the query could look like:

UPDATE dhh_ingredient, dhh_purchaseorderingredient
SET = currentStock = currentStock - amount
WHERE dhh_purchaseorderingredient.INGREDIENTingredientName = orderIngredientName
AND ingredientName = orderIngredientName
AND dhh_purchaseorderingredient.PURCHASEpurchaseOrderNo = NEW.purchaseOrderNo
AND NEW.status = 'DELIVERED';

No need for local variables or any loops, because the update query does a join and the NEW values are used directly in the query.

So your entire stored procedure becomes simply:

BEGIN

    UPDATE dhh_ingredient, dhh_purchaseorderingredient
    SET = currentStock = currentStock - amount
    WHERE dhh_purchaseorderingredient.INGREDIENTingredientName = orderIngredientName
    AND ingredientName = orderIngredientName
    AND dhh_purchaseorderingredient.PURCHASEpurchaseOrderNo = NEW.purchaseOrderNo
    AND NEW.status = 'DELIVERED';

END

which is easy to understand, maintain and debug - a better solution.

Trigger Loop keeps putting currentStock on 0

1 Answers1