MySQL update one row when another changes

Question

I have a table with products.

artID   artName stock order

1   cables *100 0
2   cables *1 75

What should I do to asociate these 2 rows and get the stock in sync. The provider sells the cables in bulks on 100 units, and the retail store sells then as unit. The idea is: when "cables *1" run nearly out of stock set the order row to 1 so the employees know they need to buy more cables. I have a relations table defining multiple types of relations between articles (if this server for something) Obviously this is a very simplified version of the db.

I'm working with php. But any language agnostic idea will serve.

Answer 1

I think you should create a trigger on your table that will be fired on UPDATE and will change the value after updating first row.

Here's the docs of how to use triggers in MySQL.

Answer 2

Have you considered using a trigger?

See

MySQL Trigger

and

Databasedesign-resource