Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enforce business rules with indexed views and a two-row table


Enforce business rules with indexed views and a two-row table

Author
Message
dcdanoland
dcdanoland
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 166
Comments posted to this topic are about the item Enforce business rules with indexed views and a two-row table
Alex Fekken
Alex Fekken
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 460
Nice out-of-the-box thinking. Initially I didn't have a clue where it was going.

You can probably speed this up for tables with many rows by having an indexed modified date in the Region and Office tables and using that in the WHERE clauses of two similar views: one that deals with recently changed Regions and one that deals with recently changed Offices (or just one view if SQL Server is smart enough in dealing with an OR between the two indexed modified date columns). That way you only need to join and check recently modified Regions and Offices instead of all of them.
Alex Fekken
Alex Fekken
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 460
... or using a (pre-CROSS JOIN) UNION of the two SELECTs of course...
Louis Hillebrand
Louis Hillebrand
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1587 Visits: 3318
Nice way to enforce business rules on several tables into one rule.
It is also impossible to make an office active when the region is inactive.

My way would be to use an UPDATE-trigger on region and office.
The region version:
CREATE TRIGGER dbo.TR_InvalidRegionIsInactiveButOfficeIsActive ON dbo.Region FOR UPDATE
AS
BEGIN

IF EXISTS (
SELECT 1
FROM Inserted
INNER JOIN dbo.Office
ON Inserted.RegionId = dbo.Office.RegionId
WHERE Inserted.IsActive = Convert(bit, 0)
AND dbo.Office.IsActive = Convert(bit, 1)
)
BEGIN
RAISERROR ('Cannot disactivate Region with an active Office', 16, 1)
ROLLBACK TRANSACTION
END

END


The trigger on office would be of a similar structure.

This will give you the opportunity to issue a more descriptive error message.
dcdanoland
dcdanoland
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 166
Louis-644449,

First of all, I should have made more clear that an AFTER trigger is always an option.

I agree that triggers allow for more friendly error messages.

I think the technique I outlines is good for situations where triggers are not desirable. Some organizations simply don't like triggers.


-Dan
dcdanoland
dcdanoland
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 166
Alex Fekken,

I like your performance suggestions. I envision using the indexed view technique on small reference tables where performance might not be as important. I think on large, frequently used tables your performance improvements would be well-advised.

-Dan
athosfolk
athosfolk
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 25
Alternatively you could have a computed column on the Region table which is the id of the region when it is active and the negative of the id when it is inactive (((2*isActive)-1)*RegionID). Have this as the column the foreign key looks up. Then have a constraint on the office table that the RegionID column multiplied by the Active column should always be positive ((RegionID*isActive)> 0).

This should stop active offices being in inactive regions (although I havent tested it) and all the logic is then kept in the two tables in question.

Athos
RonKyle
RonKyle
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2179 Visits: 3438
In my opinion these kinds of low-level integrity issues are best handled by triggers similar to the ones above. The comment about some organizations not liking triggers could be made about foreign keys. But organizations need to be shown the advantages of the triggers. The decision makers can then decide the risk. The option to further complicate the database design with dummy tables and indexes that could be avoided with the addition of a trigger seems to me also undesireable. Finally, the parent table can also have a trigger. If a region is inactivated, a trigger on that table inactivates all the offices in the region. (It's unlikely that would happen in this case, but there are many things that might be made inactive that would consequently cause their children to also become inactive).



dcdanoland
dcdanoland
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 166
RonKyle,

I can't really dispute your points regarding the benefits of triggers. One argument that could be made in favor of the technique I described is that it doesn't require transact-sql programming. Such an argument would have to contend that a view does not constitute programming, which I think is debatable. I've been on projects that prohibited stored procedures because the thought was that they were too difficult to teach to developers. I thought it was silly at the time, but darned if I haven't seen that stored procedures can be a difficult concept for some to grasp. I think that goes double for triggers. You would hope a DBA would write a trigger, but that doesn't always happen. Perhaps the Mary Hartman, Mary Hartman technique is something that developers can grasp better than triggers. Or (more likely) it's just a cool trick I wanted to share.

-Dan
Jonathan AC Roberts
Jonathan AC Roberts
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1862
So in summary:
If you have an integrity rule that you want to enforce on a database and you can write a query that returns one or more rows if there is something that invalidates this rule. Just write the query with schema binding cross joining to a two row table and add a unique index to it.
Yes, that's a pretty neat way enforcing almost any integrity constraint you want on your database. You could use it to catch the problem where you have an EffectiveFromDate and EffectiveUntilDate on your table and you don't want any rows with overlapping dates.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search