MYSQL trigger to update or insert based on if statements

Question

I have two tables, tbl_stock and tbl_stockEntry, those two tables are identical.

They have these fields: warehouse cultivar variety quantity

when an entry is entered in to the tbl_stockEntry table I would like it to either update the matching entry in the tbl_stock table or create a new entry if there is no matching entry.

But I need it to match 3 fields, warehouse, cultivar and variety.

This is my trigger to create the entry, but I can't seem to get it to insert a record if none is found.

BEGIN
  UPDATE tbl_stock
     SET quantity = quantity + NEW.quantity
   WHERE vegetableCultivar = NEW.vegetableCultivar
   AND warehouse = NEW.warehouse;
END

I have tried this and but it doesnt work:

BEGIN
  UPDATE tbl_stock
     SET quantity = quantity + NEW.quantity
   WHERE vegetableCultivar = NEW.vegetableCultivar
   AND warehouse = NEW.warehouse;
     if sql%rowcount = 0 then  
    -- nothing was updated, so the record doesn't exist, insert it. 
    insert into tbl_stock (warehouse, vegetableCultivar, vegetableVariety, quantity)
            values (NEW.warehouse, NEW.vegetableCultivar, NEW.vegetableVariety, NEW.quantity);
  end if;
END

If anyone knows how I could accomplish this I would really appreciate some assistance.

The reason I need all 3 matched is that there should be multiple warehouses, each with the same item, so warehouse 1 and warehouse 1 could both have the same cultivar and variety, so if i only match one or two of the fields I will have the wrong entries being updated. — Lance Scott
– Lance Scott, Commented Oct 1, 2018 at 1:25

Lance Scott · Accepted Answer · 2018-10-01 01:50:00Z

1

Ok this is working:

BEGIN
  UPDATE tbl_stock
     SET quantity = quantity + NEW.quantity
   WHERE vegetableCultivar = NEW.vegetableCultivar
   AND warehouse = NEW.warehouse;

    IF ROW_COUNT() = 0 THEN  
    INSERT INTO tbl_stock (warehouse, vegetableCultivar, vegetableVariety, quantity)
  values (NEW.warehouse, NEW.vegetableCultivar, NEW.vegetableVariety, NEW.quantity);
  end if;
END

answered Oct 1, 2018 at 1:50

Lance Scott

517 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL trigger to update or insert based on if statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related