How to best maintain status in a parent-child relationship
===

I couldn't figure out how to word the title of this question to make it clear, so here's my best shot at an explanation:

My client has a series of tables which are in parent-child relationships with each other. For example:

    CREATE TABLE dbo.First_Level (
        id INT IDENTITY(1, 1) NOT NULL,
        name VARCHAR(20) NOT NULL,
        active BIT NOT NULL DEFAULT(1),
        CONSTRAINT PK_First_Level PRIMARY KEY CLUSTERED (id)
    )
    
    CREATE TABLE dbo.Second_Level (
        id INT IDENTITY(1, 1) NOT NULL,
        first_level_id INT NOT NULL,
        name VARCHAR(20) NOT NULL,
        active BIT NOT NULL DEFAULT(1),
        CONSTRAINT PK_Second_Level PRIMARY KEY CLUSTERED (id),
        CONSTRAINT FK_SecondLevel_FirstLevel FOREIGN KEY (first_level_id) REFERENCES dbo.First_Level (id)
    )
    
    
    CREATE TABLE dbo.Third_Level (
        id INT IDENTITY(1, 1) NOT NULL,
        second_level_id INT NOT NULL,
        name VARCHAR(20) NOT NULL,
        active BIT NOT NULL DEFAULT(1),
        CONSTRAINT PK_Third_Level PRIMARY KEY CLUSTERED (id),
        CONSTRAINT FK_ThirdLevel_SecondLevel FOREIGN KEY (second_level_id) REFERENCES dbo.Second_Level (id)
    )

The tricky part is that if a parent is marked as no longer being active (active = 0) then any children under it should also be considered as no longer active. However, a child **can** be marked as no longer active even if the parent is still active.

I'm looking for suggestions on how best to enforce these business rules. I could overlay views which would then look at all parents to determine the final active flag for any given row, but my client already has a great deal of code written around these tables.

I could enforce the rules through triggers, but I'm hoping that there is a more elegant way of doing this.

Thanks!