Thread: "Locking rows"
I have a customer requirement/desire. The system is (among other things)
essentially a employee time sheet. The manager wants for an employee to not
be able to modify a given row in the table they enter time into once it is
committed. I personally see issues with this, but I am willing to try to give
him what he wants. If it creates issues we can sort them out, once he sees
the issues.
The only way I see to do this is to add a column (call it lock). I will
then set this up as a default entry with a value of TRUE. The form the
employee uses to enter this will, of course, not display this column. The
I will create a function that on UPDATE, checks for the presence of the 1 in
this row, and rejects the update. Then I will give the manager a form where
he can set this flag to FALSE.
Looks ugly, and convulsed to me.
Is here a more "database native" way to handle this?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
On 8/12/19 10:51 AM, stan wrote: > I have a customer requirement/desire. The system is (among other things) > essentially a employee time sheet. The manager wants for an employee to not > be able to modify a given row in the table they enter time into once it is > committed. I personally see issues with this, but I am willing to try to give > him what he wants. If it creates issues we can sort them out, once he sees > the issues. > > The only way I see to do this is to add a column (call it lock). I will > then set this up as a default entry with a value of TRUE. The form the > employee uses to enter this will, of course, not display this column. The > I will create a function that on UPDATE, checks for the presence of the 1 in > this row, and rejects the update. Then I will give the manager a form where > he can set this flag to FALSE. > > Looks ugly, and convulsed to me. > > Is here a more "database native" way to handle this? Depends on who is doing the database record changes. In other words are there defined roles: https://www.postgresql.org/docs/11/sql-createrole.html for the object(table) and the entity working with the table? -- Adrian Klaver adrian.klaver@aklaver.com
On 8/12/19 1:07 PM, stan wrote: > On Mon, Aug 12, 2019 at 12:14:25PM -0700, Adrian Klaver wrote: >> On 8/12/19 10:51 AM, stan wrote: >>> I have a customer requirement/desire. The system is (among other things) >>> essentially a employee time sheet. The manager wants for an employee to not >>> be able to modify a given row in the table they enter time into once it is >>> committed. I personally see issues with this, but I am willing to try to give >>> him what he wants. If it creates issues we can sort them out, once he sees >>> the issues. >>> >>> The only way I see to do this is to add a column (call it lock). I will >>> then set this up as a default entry with a value of TRUE. The form the >>> employee uses to enter this will, of course, not display this column. The >>> I will create a function that on UPDATE, checks for the presence of the 1 in >>> this row, and rejects the update. Then I will give the manager a form where >>> he can set this flag to FALSE. >>> >>> Looks ugly, and convulsed to me. >>> >>> Is here a more "database native" way to handle this? >> >> Depends on who is doing the database record changes. >> >> In other words are there defined roles: >> >> https://www.postgresql.org/docs/11/sql-createrole.html >> >> for the object(table) and the entity working with the table? >> > OK, maybe I could set up the "user" role, such that it does not have the > UPDATE permission on this table, just the INSERT one? Of course the "admin" > role would have the UPDATE attribute for this table. 'user' should also have SELECT if you want them to see their records. > > Make sense? Yes. > -- Adrian Klaver adrian.klaver@aklaver.com