Using Views to Enforce Business Rules

By David Moloney on 9 April 2007 | Tags: INSERT

A view is most commonly thought of as a SELECT statement. Most developers will simply create a view to "group" complex SELECT statements for reuse within another view or stored procedures. It makes typing easier! But the really power of views is their ability to implement business rules.

To fully understand how this is possible, we must go back to basics. A view in the relational model has as much standing as a relation (table). This means it can be treated in EXACTLY the same way. You can insert, update and delete data from a view, add or remove columns and most importantly add constraints to the view.

The relational model has four types of constraints that can be used to implement business models and rules.

1. Domain/Type (Attribute/column)
2. Tuple (Row)
3. Relation (Table)
4. Database (Multiple tables)

SQL Server handles the first 3 fairly well but is limited to only one type of Database constraint: The "Foreign Key".

While extremely useful, the foreign key is the simplest form of database constraint. Practically, most business models will need much more complexity than a simple Parent-Child relationship. This is where views can be used.

Let's use an example where a business has clients that generate invoices. Each invoice belongs to a particular client. The business categorizes their clients based on spending limits. They want to restrict the total of each invoice to ensure that certain clients do not exceed a limit.

This gives us 3 tables:

• Client
• SpendingType
• ClientInvoice

create table SpendingType(
SpendingType varchar(25) NOT NULL primary key, 
InvoiceLimit money not null)
go
create table Client(
ClientID int not null primary key, 
ClientName varchar(50)
	, SpendingType varchar(25) not null references SpendingType (SpendingType))
go
create table ClientInvoice(
InvoiceID int not null, 
ClientID int not null, 
TotalInvoice money not null
	, primary key (InvoiceID, ClientID), 
foreign key (ClientID) References Client (ClientID))
go

This yields us this entity-relationship diagram (ERD):

Given this ERD, we can see there is nothing to enforce our "Spending Limit" rule.

Enter the view...

create view ClientInvoice_SpendingConstraint
as
select 	InvoiceID, 
ClientID, 
TotalInvoice
from dbo.ClientInvoice CI
where exists 
	(Select 1 
from dbo.Client C 
	inner join dbo.SpendingType ST on C.SpendingType = ST.SpendingType
	where C.ClientID = CI.ClientID 
and  TotalInvoice <= ST.InvoiceLimit)
with check option

Notice the "with check option". This tells SQL Server to enforce the constraints defined by the view. There are several limitations to creating update-able views which practically can be summarised into 2 golden rules.

1. Express the entire table. Declare all columns in the underlying table in the view definition.
2. Don't touch yourself. Never reference the primary table (in our example the ClientInvoice table) in the constraint (WHERE).

When this view is presented to the user, any INSERT or UPDATE into this view must satisfy our rule. Failure to do so will result in an exception being thrown by SQL Server. Because the constraint is expressed as a set, the view can handle multiple row insert and updates effortlessly thus ensuring the ACID principle. Watch the execution plan for the successful insert/update to see how efficiently SQL Server processes the rule.

We can test the view using these statements:

insert SpendingType values ('Standard', 1000)
insert SpendingType values ('Premium', 5000)
insert Client values (1, 'David', 'Standard')
insert Client values (2, 'Peter', 'Premium')
go

--David is under 1000
insert ClientInvoice_SpendingConstraint values (1,1,600)
/* Result: (1 row(s) affected)  */

--David is over 1000 (bad)
insert ClientInvoice_SpendingConstraint values (2,1,1600)

/* Result: The attempted insert or update failed because the target view either 
specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and 
one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. */


--David is 1000 (good)
insert ClientInvoice_SpendingConstraint values (2,1,1000)
/* Result: (1 row(s) affected) */
Go

-- Update that violates the rule.
update ClientInvoice_SpendingConstraint set TotalInvoice = 1001 where InvoiceID = 2

/* Result: The attempted insert or update failed because the target view either 
specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and 
one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. */

go
--Peter is under 5000
insert ClientInvoice_SpendingConstraint values (3,2,2600)
/* Result: (1 row(s) affected) */
go
Select * from ClientInvoice
Go

Most developers would choose a stored procedure or a trigger to implement this rule. But consider the advantages using the view gives:

1. Set based and Optimised. The view is compiled and BCP and BULK INSERT friendly.
2. Abstraction. The view provides the possibility to change business rules very quickly with minimal physical impact.
3. Tool friendly. Extracting view metadata is a very common feature.

The only downside I see is the error that SQL Server throws. The error message is ugly without any detailed information.