Using ROWVERSION to enforce business rules

Suppose that you need to enforce the following business rule: contracts cannot be changed after you have started working on them (let us assume that that particular business operates in the perfect world).  You can use a ROWVERSION column, a persisted computed one, and a foreign key constraint to implement this rule, and I will demonstrate how, and yes, alternatively it can be done with triggers or stored procedures. Comparison of constraints vs. triggers for this problem is beyond the scope of this short post.

The tables and sample data are as follows:

CREATE TABLE Data.Contracts(

  ContractID INT NOT NULL

    CONSTRAINT PK_Contracts PRIMARY KEY(ContractID),

  SomeData VARCHAR(50) NOT NULL,

  VersionStamp ROWVERSION,

  VersionStampAsBinary AS CAST(VersionStamp AS BINARY(8)) PERSISTED,

  CONSTRAINT UNQ_Contracts UNIQUE(ContractID, VersionStampAsBinary)

)

GO

CREATE TABLE Data.ContractActivity(

  ContractActivityID INT NOT NULL

    CONSTRAINT PK_ContractActivity PRIMARY KEY(ContractActivityID),

  ContractID INT NOT NULL,

  ContractVersionStamp BINARY(8),

  SomeData VARCHAR(50) NOT NULL,

  CONSTRAINT FK_ContractActivity_Contracts

    FOREIGN KEY(ContractID, ContractVersionStamp)

    REFERENCES Data.Contracts(ContractID, VersionStampAsBinary)

)

GO

INSERT INTO Data.Contracts(ContractID, SomeData)

SELECT 1, 'Just do smth' UNION ALL

SELECT 2, 'TBD';

GO

Note: I will explain why I need a persisted computed column later.

As long as there is no activity for the contract, you can modify it:

UPDATE Data.Contracts

  SET SomeData = 'To be determined later'

  WHERE ContractID=2;

But when you record the activity on your contract, you need to copy the version of your contract, as follows:

DECLARE @ContractID INT;

SET @ContractID=2;

INSERT INTO Data.ContractActivity(

  ContractActivityID,

  ContractID,

  ContractVersionStamp,

  SomeData)

SELECT 1, @ContractID, VersionStamp, 'Work started'

FROM Data.Contracts

WHERE ContractID=@ContractID;

Note: You cannot explicitly insert values into ROWVERSION columns, so the contract version is stored as BINARY(8) in the child table. Because the column types on both sides of a foreign key must match, I need to store the contract version as BINARY(8) in the parent table too, which is implemented as a computed column. Also if a foreign key refers to a computed column, it must be persisted.

As soon as you have activity on you contract, you can no longer modify it:

UPDATE Data.Contracts

  SET SomeData = 'To be determined by 5/12/2009'

  WHERE ContractID=2;

Msg 547, Level 16, State 0, Line 1

The UPDATE statement conflicted with the REFERENCE constraint "FK_ContractActivity_Contracts". The conflict occurred in database "Test", table "data.ContractActivity".

The statement has been terminated.

The reason is simple: when you try to modify a contract, SQL Server will automatically up its version, and it will no longer match the recorded contract's version in the child table.

But  you can still modify other contracts:

UPDATE Data.Contracts

  SET SomeData = 'To be determined by 5/12/2009'

  WHERE ContractID=1;

(1 row(s) affected)

Note: the price tag for the solution may be steep – you have to create a unique index on a highly volatile column ROWVERSION.

This continues the series on denormalizing, which started with this post about working hours and appointments