This seems like what triggers are for and how we do similar type things. Update trigger on order does select for update on the inventory and increases/decreases it as appropriate.

I don't think you really need that even. An indexed lookup is fast and you don't need to store a computed quantity generally.