Hello,

I have some problems about creating a logical deletion(including cascade) script for a SQL Server database.

The logical deletion operation is simple for tables that are not connected by FK with other tables.

e.g
UPDATE MyTable SET IsDeleted = True WHERE Id = 1
instead of
DELETE FROM MyTable WHERE Id = 1

But, when it comes to tables connected by FK, it is a little difficult to implement this.

Did someone had this problem?

Thanks

Modify your selects to implement this.
A simple

WHERE tblParent.IsDeleted IS FALSE

will be enough.

Or, if you want to avoid that, implement an ON DELETE trigger that will check this condition and drop the rows from the child table.

I think that the trigger is the best solution.

The select works too, but it is time consuming.

Thanks

What I don't like about trigger, is that they work a bit like by magic.
When you have a lot of them, and you forget about them, you can spend 1 day trying to find why the dependent rows are deleted...

But they sure are handy...

Well, it might be better to use a recursive stored procedure, something like that:

Procedure  LogicalDelete(TableName, RecordId)
Begin
        FOR (Get all connected tables)
              LogicalDelete(ChildTableName, ChildRecordId)
        END FOR;
        UPDATE TableName SET IsDeleted = True;
End

I agree, triggers are a little bit tricky when it comes to debug.

Thanks