Re: Limiting/disallowing changes of certain columns

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.