0

I'm working with mySQL on a project for restaurant. I have two tables one is named "DISH" and the other is "INGREDIENTS",As you know a dish is composed of several ingredients.

What I want to do is organize the orders of all the customers and number the dishes that were ordered and therefore know the amount of ingredients that are needed to prepare that dish after that i need to substract from the storage in this case "INGREDIENTS" table the amount of ingredients that were used.

to my mind only come this:

UPDATE INGREDIENTS SET stock= stock-ingredients_used WHERE [i don't know what to write here]

How could i do at the moment of increase the amout of dishes in the table DISH substract from the stock column at the INGREDIENTS table the ingredients used in that dish ?

Rafael Leonardo Carvajal
  • 7
  • 4

1 Answers1

0

You can use a trigger that updates the stock after or before inserting data on another table.

Here is an example:

CREATE TRIGGER my_trigger BEFORE INSERT ON dish 
FOR EACH ROW
BEGIN
  UPDATE ingredients SET stock = (stock - NEW.used) WHERE ingredient = NEW.ingredient;
END;

For more information: MySql Official Reference

Jáder Alves
  • 16
  • 1
  • You can do in command line or if you use workbench, you can select the table dish and go to subtab triggers. – Jáder Alves May 04 '20 at 04:24
  • you wrote FOR EACH ROW, i want to say every dish has its own ingredients that other dish may not share, let's say we have pizza and hot dog when i order a pizza i can't modify the ingredients of hot dog, i just need to change the values of the ingredients that belong to a specific dish – Rafael Leonardo Carvajal May 04 '20 at 04:25
  • I think a good solution is to use three tables, ingredients, dishes and orders. The trigger must be used on table orders and get the ingredients of the dishes on the order. Here is an example of triggers with select statement https://stackoverflow.com/questions/6048549/mysql-trigger-update-with-select-from-another-table – Jáder Alves May 04 '20 at 04:51