[Translation] how to use an index view and a table with only two rows to restrict business rules

Assume that a business rule does not allow certain conditions. in addition, foreign keys are not allowed to be used. In this case, the instead of trigger can be used as an alternative answer. However, such trigger may cause more trouble in the future. Another solution is to connect an index view with a unique index to a dummy table with only one row.

Suppose you have two tables, region and office.

 

Region table

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 table

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	Minneapois	1	1/1/2009	Null
4	4	Atlanta	1	1/1/2009	Null
5	5	Byrd Station	1	1/1/2009	Null

 

How do you ensure that the active Office (isactive = 1) only belongs to the active region?

The table mentioned above requires some kind of referential integrity. regionid is the foreign key of the office table. The check constraint ensures that when expirationdate is not null, it will never be 1. but how can we prevent isactive office from being a region with isactive = 0? We can redesign the table or use the instead of trigger, but it is more convenient to use an index view with a unique index to connect to a two-row table.

 

Duplicaterows table

The duplicaterows table contains two rows:

Duplicaterowindex	Duplicaterowvalue
1	Duplicate row
2	Duplicate row

 

The only requirement for this table is that the table contains at least two rows, and its structure and content are not important. This table can even have only one column and the values in each row are different. I use this method to name the table because the name "Duplicate row" does not seem to be confusing, and such a name will not cause other DBAs to delete the table.

 

Invalidregionisinactivebutofficeisactiveview

The business rule is that if the region is not active, the office of the activity cannot be associated with it. In other words, you cannot close an office in a region or an active area unless you set isactive to 0 or assign this office to another region (region ), the following view shows an example where the isactive of region is 0 and Its offce is 1. This violates the preset business rules. Cross join with DBO. duplicaterows is performed to realize that if the WHERE clause meets the conditions, at least two rows are returned.

 
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)
 

The final finishing touch is to create a unique index. The unique index is used to prevent duplicate rows, but this view performs cross join with a two-row table, so that if you want to return results, returns the result of two rows. however, this violates the unique index, so this situation will never happen.

 
Create unique clustered index ix_regioninvalidofficeisactiveview_regionid on DBO. partition (regionid ASC) with (pad_index = OFF, partition = OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF, drop_existing = OFF, online = OFF, allow_row_locks = on, allow_page_locks = on) on [primary]
 

The final result is not against the business rules.

 

Test: Disable the Antarctica Area

The guys at the Headquarters decided to close the Antarctica area. The following statement sets the isactive value of region to 0 regardless of the state of the associated office.

 
Update DBO. region set DBO. region. isactive = convert (bit, 0), DBO. region. expirationdate = getdate () Where DBO. region. regionname = N'Antarctica'
 

After execution, the following error occurs:

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

Before closing the Antarctica area, The byrd station office must be set to inactive or assigned to other areas because I do not want to dismiss anyone in this office, so I split this office into other regions.

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

Once no active offices are associated with the Antarctica region, I can use the update statement to close the Antarctica region.

 

How to implement this technique in your database

The following tips help you achieve this in the database:

1. Create a DBO. duplicaterows table and insert two data entries.

2. Write a query that violates business rules and can return results

3. Perform cross join with DBO. duplicaterows in this query

4. Create a view containing the schemabinding parameter and the preceding query statement.

5. Create a unique index on the View

 

Summary

Using the index view and a two-row table to join may not be the most effective way to implement business rules, but using this method can avoid using complex instead triggers. If Microsoft provides the "Before trigger" to cancel a query that violates business rules before execution, this method is not required. The above technique can be seen as a helpless man-made "before trigger ".

 

Link: http://www.sqlservercentral.com/articles/Business+Rules/91924/

Translated by: careyson