Can an SQL constraint be used to prevent a particular value being changed when a condition holds?

Question

I know that SQL constraints can force data to meet validity criteria. However, what about criteria such as "Student's grade can only be updated when the 'finalised' flag is false"? Do such update criteria have to be handled by the application?

Answer 1

A trigger, a constraint, and an additional column.

Starting from the end:

1. The additional column stores the value that is to be 'fixed':

   ALTER TABLE ADD SavedGrade int
   

2. The constraint restricts the change of the Grade column:

   ALTER TABLE Students
   ADD CONSTRAINT CK_Grade CHECK (Finalised = 'false' OR Grade = SavedGrade)
   

3. The trigger updates the additional column when the Grade column gets updated (the following is for SQL Server):

   CREATE TRIGGER StudentsFinaliseGrade
   ON Students AFTER INSERT, UPDATE
   AS
   IF UPDATE(Grade)
     UPDATE Students
     SET SavedGrade = i.Grade
     FROM inserted i
     WHERE i.ID = Students.ID
       AND i.Grade <> i.SavedGrade
   

So, as long as Finalised = 'false', the Grade column may be changed. When it is changed, the value is immediately stored into the SavedGrade column. (We are updating SavedGrade directly, because otherwise the constraint wouldn't allow us to set Finalised to 'true'.) As soon as Finalised is set, you can no longer change the Grade column because of the constraint.