For what you are describing you would want to use a trigger on the table where you want the soft delete to cascade from.

Here are a few references on triggers:

MSSQLTips

Microsoft's documentation

Triggers have their place but they can quickly get out of hand when triggers fire other triggers or when the trigger execution takes a significant amount of time to complete.

Additionally performance degradation can be a factor with triggers as they happen synchronous when an insert/update/delete statement is made on the table which can extend the execution time of the statement.

Microsoft's Nested triggers

Info from Paul White on triggers

DML triggers for multiple data rows

For what you are describing you would want to use a trigger on the table where you want the soft delete to cascade from.

Here are a few references on triggers:

MSSQLTips

Microsoft's documentation

Triggers have their place but they can quickly get out of hand when triggers fire other triggers or when the trigger execution takes a significant amount of time to complete.

Additionally performance degradation can be a factor with triggers as they happen synchronous when an insert/update/delete statement is made on the table which can extend the execution time of the statement.

Microsoft's Nested triggers

Info from Paul White on triggers

DML triggers for multiple data rows