From: hubert depesz lubaczewski Sent: Wednesday, October 26, 2016 10:46 AM To: Frank Millman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Locking question
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. Thanks Frank
