THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

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 


and continued with this post about running totals.

 

and a post about Storing intervals of time with no overlaps
Published Tuesday, April 28, 2009 10:46 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul White said:

Alex,

A fascinating read and an ingenious idea!  It is a bit sad about the price tag.  Great post nonetheless.

While esoteric solutions are on the agenda: did you know that the same effect can be produced, without the unique index on the row-version, by using an indexed view?

The view (with check option) joins the two tables on the contract id and includes a case statement which returns 1 if the row-versions match, and attempts to divide by zero if the row-versions do not match.

The price of maintaining the indexed view may also be steep!  The additional restrictions on SET options required when updating the base tables may prove to be the deciding factor, as is so often the case.

I have a script to demonstrate the above, if it is not clear from the text, but I didn't want to just post random code at you!

Cheers,

Paul

April 29, 2009 7:14 AM
 

Alexander Kuznetsov said:

Hi Paul,

Sounds very interesting, can you please post your code.

April 29, 2009 8:59 AM
 

Paul White said:

It's just hacked together, but hopefully it is readable...

--drop view dbo.vw_ContractActivity1

--drop table dbo.ContractActivity, dbo.Contract

create table dbo.Contract (row_id INT IDENTITY(1,1) PRIMARY KEY, data varchar(50) not null, row_version ROWVERSION NOT NULL);

create table dbo.ContractActivity (row_id INT IDENTITY(1,1) PRIMARY KEY, contract_row_id INT NOT NULL FOREIGN KEY (contract_row_id) REFERENCES dbo.Contract(row_id), contract_row_version BINARY(8) NOT NULL);

create unique index i on dbo.ContractActivity(contract_row_id, row_id);

go

create view dbo.vw_ContractActivity1

with schemabinding

as

select a.contract_row_id, a.row_id, 1 / case when convert(rowversion, a.contract_row_version) = c.row_version then 1 else 0 end as do_row_versions_match

from dbo.Contract as c

join dbo.ContractActivity as a

on a.contract_row_id = c.row_id

with check option

go

-- materialize the view

create unique clustered index c on dbo.vw_ContractActivity1 (contract_row_id, row_id);

go

-- add a contract

insert dbo.Contract (data) values ('Contract 1 details');

-- can update

update dbo.Contract

set data = 'Contract 1 - updated ok'

from dbo.Contract

where row_id = 1;

-- add some activity

insert dbo.ContractActivity (contract_row_id, contract_row_version)

select row_id, convert(binary(8), row_version)

from dbo.Contract

where row_id = 1;

-- fails

update dbo.Contract

set data = 'Contract 1 new details'

from dbo.Contract

where row_id = 1;

-- still ok to add more activity

insert dbo.ContractActivity (contract_row_id, contract_row_version)

select row_id, convert(binary(8), row_version)

from dbo.Contract

where row_id = 1;

-- fails

insert dbo.ContractActivity (contract_row_id, contract_row_version)

select row_id, convert(binary(8), 0x0) -- doesn't match!

from dbo.Contract

where row_id = 1;

Cheers,

Paul

April 29, 2009 9:12 AM
 

Alexander Kuznetsov said:

Very nice, Paul, I love it! Thank you for posting!

April 29, 2009 9:39 AM
 

Paul White said:

You're welcome!

Paul

April 30, 2009 4:28 AM
 

Alexander Kuznetsov said:

Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table

May 4, 2009 11:35 PM
 

Paul White said:

If you say so!  :c)

May 5, 2009 8:26 AM
 

Jason Strate said:

This is extremely clever.  I like it.

May 28, 2009 3:56 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement