How to enforce a CHECK constraint across multiple tables

Question

I have a database that records breeding information for cows in Microsoft SQL Server 2012 Express. Obviously, a cow cannot be bred until after she is born, and she may be bred multiple times during her life; and I need to enforce these constraints in my database. I currently have arranged a schema according to the following diagram:

DataID is the primary key for all animals. I have attempted to implement Table-Per-Type inheritance, hence the 1-to-1 relationship between [Animals].[Master] and [Animals].[Females]. Since each female may be bred multiple times, I have set up a 1-to-Many relationship between [Animals].[Females] and [Breedings].[Breedings]

My question is: how can I enforce a rule that for all females BirthDate < Breedings.Date?

I essentially need something like the following psudocode (which I have actually put into the CHECK constraint's "expression" box and received a validation error):

[Animals].[Master].[BirthDate] < [Breedings].[Breedings].[Date]
INNER JOIN [Animals].[Master] ON
[Breedings].[Breedings].[DataID] = [Animals].[Master].[DataID]

I have also tried creating a view with the proper join, but found that CHECK constraints cannot be used in views.

So, does anyone know how I can enforce these constraints?

EDIT - I tried the advice of using triggers, but can't seen to formulate the trigger syntax correctly. Here is my code:

USE [CowInventory];
GO
CREATE TRIGGER [Breedings].[iCheckBreedingDateAfterBirthDate]
ON [Breedings].[Breedings]
FOR INSERT
AS
BEGIN
    DECLARE @CowID UniqueIdentifier
    SELECT @CowID = DataID FROM inserted;

    DECLARE @CowBirthDate Date
    SELECT @CowBirthDate = BirthDate FROM [Animals].[Master] WHERE [Master].[DataID] = @CowID

    DECLARE @BreedingDate Date
    SELECT @BreedingDate = Date FROM inserted;

    IF(@CowBirthDate > @BreedingDate)
        BEGIN
            THROW;
        END
END

According to a book I have (SQL Server 2012 Step by Step) this syntax should work perfectly. But instead, SQL Server gives me pink lines under THROW and the last END, stating Incorrect syntax near 'THROW'. Expecting CONVERSATION, DIALOG, DISTRIBUTED, or TRANSACTION. and Incorrect syntax near 'END'. Expecting CONVERSATION. I have inserted these keywords, but they change nothing.

Answer 1

I wouldn't use a trigger. Use a check constraint. Much cleaner. Remember a check constraint can call a function. Write a function that does your checking in the other table. Then call the function from the check constraint. This is the best way to achieve multi-table check constraints.