【譯】如何使用索引檢視表和一個只有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 SchemaBindingAsSELECT     dbo.Region.RegionIdFROM       dbo.RegionINNER JOIN dbo.OfficeON         dbo.Region.RegionId = dbo.Office.RegionIdCROSS JOIN dbo.DuplicateRowsWHERE      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