【譯】如何使用索引視圖和一個只有2行的表限制業(yè)務(wù)規(guī)則

    假設(shè)一個業(yè)務(wù)規(guī)則規(guī)定某些情況不允許存在.并且不允許使用外鍵進行限制，此時Instead of 觸發(fā)器可以作為備選答案，但是通常這類觸發(fā)器在以后帶來的麻煩會多于其帶來的便利。還有一種解決方式是使用包含唯一索引的索引視圖與只有一個兩行的dummy table進行連接。

     假設(shè)你有如下兩個表，地區(qū)和辦事處

Region表

Office表

你如何保證活動的(IsActive=1)的Office只屬于活動的Region?

    上面提到的表需要某種參照完整性.RegionId是Office表的外鍵，Check約束保證了在ExpirationDate不為Null的情況下，永遠不會是1.但是我們?nèi)绾畏乐笽sActive的的Office屬于IsActive=0的Region呢？我們可以重新設(shè)計表或是使用Instead of觸發(fā)器，但更方便的做法是使用一個含有唯一索引的索引視圖與一個兩行的啞表進行連接。

DuplicateRows table

    DuplicateRows表包含兩行，如下

    對于這個表的唯一要求是這個表至少含有兩行，其結(jié)構(gòu)和內(nèi)容并不重要。這個表甚至也可以是只有一列的表并且每行的值不一樣。而我這里用這種方式命名是因為“Duplicate Row”這個名字看起來不容易產(chǎn)生混淆，并且這樣的命名也不會使得其它DBA刪除這個表。

InvalidRegionIsInactiveButOfficeIsActiveView視圖

    業(yè)務(wù)規(guī)則是如果區(qū)域(Region)是不活動的，不能存在活動的辦事處(Office)與其關(guān)聯(lián)。換句話說，不能在屬于某個區(qū)域的辦事處還是active的情況下關(guān)閉這個區(qū)域，除非設(shè)置IsActive為0或是將這個辦事處分到其它區(qū)域(Region),下面的View顯示了顯示了Region的IsActive為0并且屬于它的offce的IsActive為1的例子。這違背了預(yù)設(shè)的業(yè)務(wù)規(guī)則.與dbo.DuplicateRows進行Cross Join是為了實現(xiàn)如果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)

    最后的畫龍點睛之筆是創(chuàng)建唯一索引，唯一索引是為了防止重復(fù)行,但是這個視圖與一個兩行的表進行Cross Join,這使得如果要返回結(jié)果，則返回一個兩行的結(jié)果.但這又違背了唯一索引，所以這種情況永遠不可能發(fā)生。

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]

    最后的結(jié)果是不會違背業(yè)務(wù)規(guī)則。

測試:關(guān)閉Antarctica區(qū)域

    總部的那幫家伙決定關(guān)閉Antarctica區(qū)域。下面語句在不顧與之關(guān)聯(lián)的Office的狀態(tài)的情況下設(shè)置Region的IsActive為0。

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

    當(dāng)執(zhí)行后，發(fā)生如下錯誤:

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

    在關(guān)閉Antarctica區(qū)域之前，Byrd Station辦事處必須設(shè)置成不活動的或者是分配給其它區(qū)域，因為我并不想解雇這個辦事處的任何人，所以我將這個辦事處分為了其它區(qū)域。

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區(qū)域相關(guān)聯(lián)，我就可以通過Update語句來關(guān)閉Antarctica區(qū)域了。

如何在你的數(shù)據(jù)庫中實現(xiàn)這個技巧

    下面幾部幫助你在數(shù)據(jù)庫中實現(xiàn)這個技巧：

    1.創(chuàng)建dbo.DuplicateRows table表并插入兩條數(shù)據(jù)

    2.寫一個違反了業(yè)務(wù)規(guī)則并且還能返回結(jié)果的查詢

    3.在這個查詢中與dbo.DuplicateRows進行Cross Join

    4.創(chuàng)建一個包含SchemaBinding參數(shù)和上面查詢語句的視圖

    5.在視圖上創(chuàng)建唯一索引

總結(jié)

    使用索引視圖和一個兩行的表進行連接或許并不是實現(xiàn)業(yè)務(wù)規(guī)則最有效的手段，但是使用了這種方法可以避免使用復(fù)雜的Instead of觸發(fā)器。假如微軟提供了“Before觸發(fā)器”使得違反業(yè)務(wù)規(guī)則的查詢在執(zhí)行之前就被取消的話，就不需要我這種手段了。上面的技巧可以看作是一個無奈的人實現(xiàn)的山寨版”before 觸發(fā)器”。

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

    Translated by:CareySon