Melvin Call wrote > Hello all, > > I am creating a data model that contains time-dependent data. I only need > a > snapshot solution, where we capture the initial state of all fields in the > record, and we then store subsequent changes as a new row in a child > table. > What I am looking at is creating a primary table that contains the fields > we do not need to track, or do not want to allow changes to, and a child > table that contains the ones we do need to track. My question regards the > columns in the parent table that we want to disallow changes to, or once > they have been changed to a certain value to prevent further changes. An > example would be an isvalid Boolean. Once a record has been marked invalid > (perhaps it was created by mistake), we do not want it to accidentally be > marked valid but we want to preserve it and any history associated with > it. > Other cases would be the initial creation timestamp or the initial > creation > person. > > I'm assuming that I can create a BEFORE trigger that will prevent the > changes (and probably even return a custom error), but is that the only > and/or best way to handle such a case?
Most common solution probably; its hard to state what might be best and one possible alternative is to move those "invalid" records to an insert-only archive table so they are available for reference but do not clutter up the main table. Probably the main reason you'd want to avoid a trigger is if performance was suffering intolerably. But until that happens in reality you might as well take the most common and likely least complicated solution. An aside: Look into "hstore" (or maybe json) for key-store functionality which may be a technical tool you can use in your ultimate solution. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Limiting-disallowing-changes-of-certain-columns-tp5777805p5777813.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
