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?
Sorry I can't be more specific than that, but this is still just a lot of scribbling on a blackboard at this point. However, since I know we are going to have to preserve some historical information I am thinking ahead on the best way to handle the situation.
Thanks,
Melvin