You need to create a view in your database that will compute the transactions table inventory (buy and sell) automatically.
Inventory
| Inventory ID | Product ID | Quantity | Type |
|---|---|---|---|
| I001 | P001 | 10 | Buy |
| I002 | P001 | -2 | Sell |
Sample:
Select ProductID,sum(Quantity) as RemainingQTY group by ProductID
Result Table View
| ProductID | RemainingQTY |
|---|---|
| P001 | 8 |
You can now use this view for another transaction.