Auto Update stock in Database Mysql

Question

I need to know about one thing in databases. I have product table with it's total inventory as seen in image

If any one have any idea that how can i do that?? Please Share it Thanks

question from:https://stackoverflow.com/questions/65626162/mysql-sum-and-update-between-tables

Answer 1

What you're asking about isn't technically a "relationship" in the technical sense when referring to relational databases. Such relations are things like having a table that refers to the "key" of another table, for instance relating a table with a customer's address to another table with the customer's order. Anyway, that's beyond the scope of what you asked about so to answer your question, you can do that in the application code or a trigger.

Triggers are features of the database that does something when an INSERT, UPDATE, or DELETE happens.

Something like this should work okay with minor adjustments for table/column names:

UPDATE table2 SET inventory = inventory - NEW.qty where id = NEW.id_product;

Now that only covers an INSERT; you'll want to create another trigger for Event UPDATE and probably somehow handle returned inventory or cancelled orders as well, but that's probably something you'll handle at the application level rather than in a trigger.