CODEWITHSUNDEEP
mysqldatabasedatabase-designphpmyadmin
M Uzair Qadeer
M Uzair Qadeer

Reputation: 492

Auto Update stock in Database Mysql

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

enter image description here

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

Upvotes: 0

Views: 1893

Answers (1)

Isaac Bennetch
Isaac Bennetch

Reputation: 12442

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:

phpMyAdmin trigger dialog

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.

Upvotes: 1

Related Questions