【译】如何使用索引视图和一个只有2行的表限制业务规则

    假设一个业务规则规定某些情况不允许存在.并且不允许使用外键进行限制，此时Instead of 触发器可以作为备选答案，但是通常这类触发器在以后带来的麻烦会多于其带来的便利。还有一种解决方式是使用包含唯一索引的索引视图与只有一个两行的dummy table进行连接。

     假设你有如下两个表，地区和办事处

 

Region表

   

RegionId	RegionName	IsActive	EffectiveDate	ExpirationDate
1	East	1	1/1/2009	NULL
2	West	1	1/1/2009	NULL
3	North	1	1/1/2009	NULL
4	South	1	1/1/2009	NULL
5	Antarctica	1	1/1/2009	NULL

 

Office表

RegionId	RegionId	OfficeName	IsActive	EffectiveDate	ExpirationDate
1	1	New York	1	1/1/2009	NULL
2	2	Los Angeles	1	1/1/2009	NULL
3	3	Minneapolis	1	1/1/2009	NULL
4	4	Atlanta	1	1/1/2009	NULL
5	5	Byrd Station	1	1/1/2009	NULL

 

你如何保证活动的(IsActive=1)的Office只属于活动的Region?

    上面提到的表需要某种参照完整性.RegionId是Office表的外键，Check约束保证了在ExpirationDate不为Null的情况下，永远不会是1.但是我们如何防止IsActive的的Office属于IsActive=0的Region呢？我们可以重新设计表或是使用Instead of触发器，但更方便的做法是使用一个含有唯一索引的索引视图与一个两行的哑表进行连接。

 

DuplicateRows table

    DuplicateRows表包含两行，如下

DuplicateRowIndex	DuplicateRowValue
1	Duplicate Row
2	Duplicate Row

 

    对于这个表的唯一要求是这个表至少含有两行，其结构和内容并不重要。这个表甚至也可以是只有一列的表并且每行的值不一样。而我这里用这种方式命名是因为“Duplicate Row”这个名字看起来不容易产生混淆，并且这样的命名也不会使得其它DBA删除这个表。

 

InvalidRegionIsInactiveButOfficeIsActiveView视图

    业务规则是如果区域(Region)是不活动的，不能存在活动的办事处(Office)与其关联。换句话说，不能在属于某个区域的办事处还是active的情况下关闭这个区域，除非设置IsActive为0或是将这个办事处分到其它区域(Region),下面的View显示了显示了Region的IsActive为0并且属于它的offce的IsActive为1的例子。这违背了预设的业务规则.与dbo.DuplicateRows进行Cross Join是为了实现如果Where子句满足条件，最少返回两行。

CREATE View dbo.InvalidRegionIsInactiveButOfficeIsActiveView With SchemaBinding

As

SELECT     dbo.Region.RegionId
FROM       dbo.Region
INNER JOIN dbo.Office
ON         dbo.Region.RegionId = dbo.Office.RegionId
CROSS JOIN dbo.DuplicateRows
WHERE      dbo.Region.IsActive = Convert(bit, 0)
AND        dbo.Office.IsActive = Convert(bit, 1)

    最后的画龙点睛之笔是创建唯一索引，唯一索引是为了防止重复行,但是这个视图与一个两行的表进行Cross Join,这使得如果要返回结果，则返回一个两行的结果.但这又违背了唯一索引，所以这种情况永远不可能发生。

CREATE UNIQUE CLUSTERED INDEX IX_RegionInvalidOfficeIsActiveView_RegionId ON dbo.InvalidRegionIsInactiveButOfficeIsActiveView
 (RegionId ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    最后的结果是不会违背业务规则。

 

测试:关闭Antarctica区域

    总部的那帮家伙决定关闭Antarctica区域。下面语句在不顾与之关联的Office的状态的情况下设置Region的IsActive为0。

UPDATE dbo.Region
 SET dbo.Region.IsActive  = Convert(bit, 0),
     dbo.Region.ExpirationDate = GetDate()
 WHERE dbo.Region.RegionName  = N'Antarctica'

    当执行后，发生如下错误:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.InvalidRegionIsInactiveButOfficeIsActiveView' with unique index 'IX_RegionInvalidOfficeIsActiveView_RegionId'. The duplicate key value is (5).

    在关闭Antarctica区域之前，Byrd Station办事处必须设置成不活动的或者是分配给其它区域，因为我并不想解雇这个办事处的任何人，所以我将这个办事处分为了其它区域。

UPDATE dbo.Office
 SET dbo.Office.RegionId = (SELECT dbo.Region.Regionid 
                             FROM dbo.Region 
                             WHERE dbo.Region.RegionName = N'South'
                            )
 FROM dbo.Office
 WHERE dbo.Office.RegionId =(SELECT dbo.Region.Regionid
                              FROM dbo.Region
                              WHERE dbo.Region.RegionName = N'Antarctica'
                             )

    一旦没有任何活动的办事处与Antarctica区域相关联，我就可以通过Update语句来关闭Antarctica区域了。

 

如何在你的数据库中实现这个技巧

    下面几部帮助你在数据库中实现这个技巧：

    1.创建dbo.DuplicateRows table表并插入两条数据

    2.写一个违反了业务规则并且还能返回结果的查询

    3.在这个查询中与dbo.DuplicateRows进行Cross Join

    4.创建一个包含SchemaBinding参数和上面查询语句的视图

    5.在视图上创建唯一索引

 

总结

    使用索引视图和一个两行的表进行连接或许并不是实现业务规则最有效的手段，但是使用了这种方法可以避免使用复杂的Instead of触发器。假如微软提供了“Before触发器”使得违反业务规则的查询在执行之前就被取消的话，就不需要我这种手段了。上面的技巧可以看作是一个无奈的人实现的山寨版”before 触发器”。

 

    原文链接：http://www.sqlservercentral.com/articles/Business+Rules/91924/

    Translated by:CareySon