CODEWITHSUNDEEP
mysqlsqltriggers
Dan185
Dan185

Reputation: 356

Create trigger to SUM tables and insert into another

I have an sql function, it's 

SELECT p.id, coalesce(ig.NumReceived,0) as NumReceived,
        coalesce(og.NumShipped,0) as NumShipped, p.Label,
        coalesce((p.StartingInventory-og.NumShipped+ig.NumReceived), p.StartingInventory)
        as OnHand, p.ProductName,
        p.StartingInventory, p.MinimumRequired
        from products p
        left outer join (
            select productid, sum(NumReceived) as NumReceived
            from incoming
            group by productid
        ) as ig on p.id = ig.productid
        left outer join (
            select productid, sum(NumberShipped) as NumShipped
            from outgoing
            group by productid
        ) as og on p.id = og.productid');

I did this so that I wouldn't have to create a column in products for "incoming" and I could just display the result from this but now I'm wanting to create a column in products for incoming and outgoing. If I can solve incoming, I can probably solve outgoing as well.

The products table looks like this

id(int primary key), MinimumRequired(int), StartingInventory(int), Label(text), ProductName(text), inc(int)

The inc column is all null right now as I just added it. Incoming looks like this.

id(int primarykey), SupplierId(int), ProductId(int), NumReceived(int),  PurchaseDate(date)

To give you an idea of the GUI, it's this GUI

This is what I have so far

CREATE TRIGGER inc_update
  AFTER UPDATE ON incoming
  FOR EACH ROW 
BEGIN
  UPDATE products
    SET inc = sum(NEW.NumReceived)
    WHERE products.id = NEW.ProductId;
END;

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

Presumably you want:

DELIMITER $$

CREATE TRIGGER inc_update
  AFTER UPDATE ON incoming
  FOR EACH ROW 
BEGIN
  UPDATE products
    SET inc = COALESCE(inc, 0) + NEW.NumReceived
    WHERE products.id = NEW.ProductId;
END;$$

DELIMITER ;

That is, you don't need the aggregation because you are adding the values in one at a time.

For formal correctness, you should have triggers on update and delete as well.

Upvotes: 1

Related Questions