sql-server-2008database-integrity

Hopefully simple SQL question for enforcing immutability of a column based on the value of another column


I have a table with a Value column and a Status column.

What is the simplest and most effective way to enforce the following? The idea is that money values are entered into the database and then someone says "Final" and after that I need to guarantee no changes because a billing process begins.

  1. New rows have Status='New' (i.e. fails otherwise)
  2. Value can only be updated when Status='New'
  3. Status can be changed to 'Final' exactly one time, and after that the whole row is effectively read only.

Solution

  • A trigger is the best way to enforce this.