I am creating an app for inventory management. Currently, I have 2 tables.
I have a table called “Books Stock” which contains 2 columns. The first column is called “Book Name”, and the second “Book Amount”. This is a read only table which has rows with every book as well as the amount of books we have.
The second table is called “Purchased Books” which is a table for entries. It contains the columns; Students Name, Book Purchased, Date Purchased
Whenever a student buys a book, an entry is made for the “Purchased Books” table in order to keep a record. The “Book Purchased” column is a reference to the “Book Stock” table and is uses a drop down in order to select the book which was purchased.
What I am trying to achieve is that whenever an entry is made in the “Purchased Books” column for a certain book, the “Book Amount” value is decreased by one. Essentially, whenever a book is purchased it will automatically update the stock so that it always shows the accurate amount of books I currently have in stock.
How would I be able to do this?