On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote: > Hi all > > > > I am designing an inventory application, and I want to ensure that the stock level of any item cannot go negative. > > [...]
> > What I would do, is to add trigger on inv_alloc, than when you > insert/update/delete row there, it updates appropriate row in inv_rec by > correct number. > > Then, I'd add check on inv_rec to make sure qty is never < 0. >
Thanks, depesz
I can see how that would work, but I have two comments.
1. I am writing my application to support 3 databases – PostgreSQL, sqlite3, and MS SQL Server. Because they are all so different when it comes to triggers and procedures, I am trying to avoid using them, and do as much within the application as possible.
2. I think you are suggesting maintaining a ‘balance’ column on inv_rec. This raises the question of whether or when you should create and maintain a column if the same information could be derived from other sources. I realise that this is a judgement call, and sometimes I struggle to get the balance right. Is this a situation where people would agree that it is warranted?
I would still appreciate some feedback as to whether my proposed solution would work.