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.