[SOLVED] Disallow column update based on existing value

Disallow column update based on existing value

I have the sample table below

Student	Current_Checkpoint
John Smith	1
Jane Smith	3

To prevent erroneous updates or fat-finger en-masse updates, I want to enforce the following rule

For the column Current_Checkpoint, the value can ONLY be updated to the next integer. For example, for John Smith, the next value can only be 2, and for Jane Smith, it can only be 4.

Updating John Smith's Current_Checkpoint value to 3 (or any value other than 2) should be rejected by the database.

I believe this would be an UPDATE trigger, but I would need help constructing it. Also, would a check constraint on the column make sense, or no?

Many thanks.

Solution

Yes you need a trigger to enforce this. Join inserted and deleted on the primary key and throw an error if you find incorrect rows.

CREATE OR ALTER TRIGGER CheckpointExactlyOne ON dbo.YourTable
FOR UPDATE
AS

SET NOCOUNT ON;

IF EXISTS (SELECT 1
    FROM inserted i
    JOIN deleted d ON d.YourPrimaryKey = i.YourPrimaryKey
    WHERE i.Current_Checkpoint <> d.Current_Checkpoint + 1
)
    THROW 50001, N'Must update Current_Checkpoint to exactly 1 more than existing value', 1;