1

I have two tables, orders and products. Products table has a column name Qty_On_Hand, which stores the amount of that product on is in stock. The orders table contains a row, Qty, where the amount ordered is entered. I am trying to create a trigger that updates the amount in stock in the products table from the amount that is ordered. These tables are linked by "ProductID".

UPDATE products
SET products.Qty_On_Hand = Products.Qty_On_Hand - orders. qty 
WHERE products.ProductID = orders.ProductID

Any help will be greatly appreciated!

Taha Paksu
  • 15,371
  • 2
  • 44
  • 78
Austin Holmes
  • 23
  • 1
  • 5

2 Answers2

0

This Should Do the trick. You need a Trigger that executes everytime you insert a new order.

CREATE TRIGGER  QuantityUpdate
AFTER INSERT
   ON orders FOR EACH ROW

BEGIN

UPDATE products
SET products.Qty_On_Hand = Products.Qty_On_Hand - New. qty 
WHERE products.ProductID = New.ProductID ;

END;
Jose Agustin Reinoso
  • 65
  • 1
  • 8
0

There is a lot of nice examples of how to create a trigger. This poped up in my first search: https://www.sitepoint.com/how-to-create-mysql-triggers/.

Do not forget to take into account whether order amount can be updated/deleted.

However even though I consider triggers to be great in themselves, I rarely tend to use them. Mostly because it is easy to forget about them. Then you are thoroughly searching your code to find out why is it behaving like it is, just to find out, that the code itself bears no answers. The answer is hidden in the database. Or quite the opposite, you know that the trigger is there and consider it to be a magical black box that does your work for you. Therefore you do not double check on its functionality, which could backfire.

https://stackoverflow.com/a/460343/7081773

Therefore in your place, I would move the logic into the code executing the insertion into orders (unless you are doing everything in the database only). That, of course, will require to create functions for order modification. Those functions will handle stock modifications as well. You would use them instead of direct sql queries. That would provide you better control over the code base.

Or maybe even better, do not modify the stock itself. Subtract the ordered amount when you are reading the remaining stock for the application (maybe create a view for that). Modify the stock amount only when it really changes (like when the product is shipped). That way you know the amount really in stock, how much is ordered and you can always compute how much is available (in stock - ordered = available). Of course you can go the other way around, just as you started to. It will work too. It just seems a bit more logical for my mind, as I usually do it this way...

Community
  • 1
  • 1
Viliam Aboši
  • 447
  • 2
  • 14