August 19, 2012 at 9:33 pm
Comments posted to this topic are about the item Enforce business rules with indexed views and a two-row table
August 19, 2012 at 11:49 pm
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.
August 20, 2012 at 12:06 am
... or using a (pre-CROSS JOIN) UNION of the two SELECTs of course...
August 20, 2012 at 2:16 am
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:
[Code="sql"]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[/code]
The trigger on office would be of a similar structure.
This will give you the opportunity to issue a more descriptive error message.
August 20, 2012 at 4:02 am
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
August 20, 2012 at 4:10 am
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
August 20, 2012 at 4:33 am
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
August 20, 2012 at 6:43 am
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).
August 20, 2012 at 7:34 am
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
August 20, 2012 at 7:36 am
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.
August 20, 2012 at 7:42 am
athosfolk,
Your technique is very interesting. As I understand it, your technique eliminates a semi-bogus table and an indexed view with a computed column and a check constraint. The calculations scare me, though. I understand what they do, but I think their purpose would be very confusing at first. Of course, the same could be said of my indexed view.
August 20, 2012 at 7:54 am
Jonathan AC Roberts,
I'd be curious to see how your problem would be tackled by Trigger Champions (imagine chivalrous armored knights jousting for the sake of the favored programming construct). My guess is that it can be done with a trigger. However, one possible benefit of the Mary Hartman, Mary Hartman approach is that it might be easier to grasp conceptually. I could imagine solving the problem first with an indexed view, and then, once you've verified that the logic makes sense, converting it into a trigger. I could also imagine getting to work with an indexed view and then working on something else because there's only so many hours in a day.
-Dan
August 20, 2012 at 7:59 am
(deleted).
August 20, 2012 at 8:06 am
I'd be curious to see how your problem would be tackled by Trigger Champions
The first requirement would be to know how it should be handled. Would the add be disallowed? If it would be allowed, would it activate the parent (if addtion actually active) or would it inherit the inactive status of the parent? I had a similar situation for which I used triggers to control the offices to jobsites to some other thing. The trigger even disallowed an update that activated the other thing or a jobsite if the parent was inactive. I'll provide that code in the next couple of days, but can't at the moment.
August 20, 2012 at 8:16 am
RonKyle,
To be clear, my post about using triggers vs. an indexed view for a specific problem referred to Jonathan AC Roberts's example:
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.
I think that would have been a more interesting example than the one I used in my article.
-Dan
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply