Define a custom delete rule for a parent-child relationship

Question

I am trying to create a custom delete rule based on the relationship between parent and child tables. What I would like to do is apply cascading deletion for the child if the child column "IsActive" is set to false and apply the rule "NoAction" if this column is set to true. How can i do this?

Answer 1

Based on your comments, you can write a trigger in your table, which will 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 */