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

上一篇 / 下一篇 2012-08-30 13:23:24 / 个人分类：数据库

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

"Tp7A USg$v v+QP0　　假设你有如下两个表，地区和办事处

　　Region表51Testing软件测试网1p zle9G'S&A

RegionId51Testing软件测试网T F7R5v7F,IG	RegionName :H$t fV`0	IsActive G&ciY,iE0	EffectiveDate nr @MuhY'U0	ExpirationDate
1 (WhQ9l"t;`Wu&NX0	East O/_'u6Da^0	151Testing软件测试网1Emcv)n-fU	1/1/2009 xQ3CJ!a?Qw:u-] w3}0	NULL51Testing软件测试网Km)QaLz
251Testing软件测试网+\_Q] Gfx	West	1 -t'Br[-K9z\O)X0	1/1/200951Testing软件测试网2K!t`c'Zp$p)P	NULL
351Testing软件测试网7t$HoQe'g	North51Testing软件测试网7G.a$itXZ,Sz	1 {K]v FAQ$X3R\_0	1/1/2009 (AJA;j.\-U0	NULL VTz"~3Xq {0
4	South (_,O:h l0u(r;n~K \|0	1 4p]NR\|-QI;E(Z0	1/1/200951Testing软件测试网VU2j pkO%S	NULL
5 V*^1D)zJ\l[0	Antarctica51Testing软件测试网v#NW @Mis&\	1 X,O\| c^%\|0	1/1/2009 N*Q%}F;I9Z+smg)H0	NULL51Testing软件测试网@}}kS?M

　　Office表

RegionId 9r;] v\&Rm"N"b Q0	RegionId Q4r0r{h0	OfficeName51Testing软件测试网YuL9cU	IsActive	EffectiveDate	ExpirationDate
151Testing软件测试网u(nTu\!a	151Testing软件测试网&o*RK wTam	New York V7i/J.\f:OO%U!c+x0	1 J#W`bXY]0	1/1/200951Testing软件测试网fl"] \|bP	NULL
251Testing软件测试网;X/X4r\,bj*i	2 rWhBT0	Los Angeles $\$rJ/xzZ+WC0	151Testing软件测试网2foxC[S	1/1/200951Testing软件测试网C @"] WAC.U[	NULL
3	3	Minneapolis	1 xKC.nrZE!l^h0	1/1/2009 -?)Tc7qD4mpJE0	NULL
4 X\s.w8zU Gi;`i0	451Testing软件测试网(W{1fS9xou	Atlanta k+UcR(}5q7I.tR#Ty0	1 ?~;Lk#x$Ff*y^l0	1/1/2009 "t+atxJ\0	NULL51Testing软件测试网2}}-p4jKC[
5 7@o#y+HY&Cw0	5 ._8zf:SJ6y0	Byrd Station B ~p$WP g.Q TPP0	151Testing软件测试网j3t3V:@;}p N	1/1/2009 9D,Yb6rhC0	NULL 4h#gFt!S0

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

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

　　DuplicateRows table51Testing软件测试网'^.pq/Od*H

　　DuplicateRows表包含两行，如下51Testing软件测试网`Uf2G)I0D'J

DuplicateRowIndex	DuplicateRowValue c~WdA+H W$D}+d*d(Mu0
1	Duplicate Row51Testing软件测试网4u9P X#Yya
2 TK(BWl"t\|\Q0	Duplicate Row51Testing软件测试网Q VG4S s![a

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

　　InvalidRegionIsInactiveButOfficeIsActiveView视图

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

6w W#et;y0E+H051Testing软件测试网2v@D'p X~S

CREATE View dbo.InvalidRegionIsInactiveButOfficeIsActiveView With SchemaBinding51Testing软件测试网k/o'v uh

As51Testing软件测试网-T-|} rD3bC

U N;i I)R T;J0SELECT     dbo.Region.RegionId
K9BZ5v#C D-o7t0FROM       dbo.Region
e5uj$Fw I0INNER JOIN dbo.Office51Testing软件测试网 Y+Ld|Q3cm
ON         dbo.Region.RegionId = dbo.Office.RegionId
CROSS JOIN dbo.DuplicateRows
WHERE      dbo.Region.IsActive = Convert(bit, 0)
"Tx(c3c EI Y0AND        dbo.Office.IsActive = Convert(bit, 1)

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

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, NLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

$R3ow2X7^c0　　最后的结果是不会违背业务规则。

8R| ?G{^0　　测试：关闭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'

h&nSUq.Xva0　　当执行后，发生如下错误：

5v,\n,p\o0

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).

eZ4DN QK hUW#X0　　在关闭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'
                             )

8k$mJ?$c.}.D0　　一旦没有任何活动的办事处与Antarctica区域相关联，我就可以通过Update语句来关闭Antarctica区域了。

a#v^4sn)W0　　如何在你的数据库中实现这个技巧

? nTi/O7` }L0　　下面几部帮助你在数据库中实现这个技巧：

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

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

cl4Il4\{"S/[0　　3、在这个查询中与dbo.DuplicateRows进行Cross Join

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

　　5、在视图上创建唯一索引51Testing软件测试网K8|&Z!\QlE

　　总结

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

收藏举报

TAG:

查看全部评论

bob123654

不要让那些真正对你好的人，慢慢的从你的生活中消失，无论爱情还是友情，都需要用心经营。

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

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅