Define a custom delete rule for Parent-Child relationships

Question

I'm trying to create a custom delete rule on a relationship between a parent and a child table. What I'd like to do is to apply cascade delete on the child if the child's column 'IsActive' is set to false and to apply 'NoAction' rule if this column is set to true. How can I do this?

Answer 1

Based on your comments, you could write a trigger on your table that would be something like this:

CREATE TRIGGER tr_ParentTable_Update
    ON ParentTable
    FOR INSERT, UPDATE
AS
BEGIN
    IF UPDATE(IsDeleted) BEGIN
        DELETE FROM ct
            FROM inserted i
                INNER JOIN ChildTable ct
                    ON i.ID = ct.ParentID
                        AND ct.IsActive = 0
            WHERE i.IsDeleted = 1
    END /* IF */
END /* Trigger */