CODEWITHSUNDEEP
mysqltriggers
Astronaut
Astronaut

Reputation: 7051

MySQL Trigger Sum Two Columns where userID is the same

I have the following product table

id | companyID | prodID | price | stock | stockAvailable | sumStock
1    A           2         10      5       4                    
2    B           2         50      10      5

I need to have a trigger when I update a product row that will update the sumStock.

I am new to Triggers, my attempt failed:

CREATE TRIGGER `SalesDB`.`stockSumUpdate` BEFORE UPDATE
    ON SalesDB.Product FOR EACH ROW
BEGIN
SET Product.sumStock = Product.stock + Product.stockAvailable
END

My Goal is in this case to calculate the SUM(stock) AS stockSum Where ProductID=2 in this case that would be 15 and then add that to the sumStock. Then add the stockAvailable column to that as well. So in sumStock for both collumns I would have 24.

Result would be:

id | companyID | prodID | price | stock | stockAvailable | sumStock
1    A           2         10      5       4               29     
2    B           2         50      10      5               29

Upvotes: 0

Views: 5599

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Try this syntax:

CREATE TRIGGER `SalesDB`.`stockSumUpdate` BEFORE UPDATE
    ON SalesDB.Product FOR EACH ROW
BEGIN
    SET NEW.sumStock = NEW.stock + NEW.stockAvailable
END;

That adds within a row of a table.

To get the total, you would use something like:

CREATE TRIGGER `SalesDB`.`stockSumUpdate` BEFORE UPDATE
    ON SalesDB.Product FOR EACH ROW
BEGIN
    SET NEW.sumStock = (select sum(stock + stockAvailable)
                        from products where p.prodid = new.prodid and p.id <> id
                       ) + NEW.stock + NEW.stockAvailable;
END;

Except, this still, doesn't do what you want. If you are updating multiple rows at a time, you will get different total stock values.

In other words, you are trying to update a group of rows whenever you update a single row. Gosh, when put like that, it doesn't seem like a good idea. The second set of udpates could update even more rows and so on and so on (it wouldn't happen because of the product).

Instead, create a table with product as a primary key and the stock available in that table (might be an existing table). Then update the summary table every time there is a change in this table.

Upvotes: 2

Related Questions