Just trying to get some ideas on what people do for this scenario. We have a system database(SQL Server 2008 R2) that has tables and every table has a field we can call "Deleted". It's basically a bit field if its a 1 the record is deleted, if it is a 0 it is not deleted. The field is not nullable and its default is of course 0.
We cannot allow real deletions to the database, so to get around this we set a bit field (Deleted) to true. In our application we end up with queries that look like this:
SELECT blah FROM MyTable WHERE .. AND Deleted=0
Basically we filter for records so we only get non deleted rows. Our issue is related records that need to cascade. What do people prefer, should we be doing this in the server side code so that when you delete a record it delete's (Sets the deleted bit field to true) for all related records? Or should this be a trigger that has to check this field and sets the bit field for all related records to 1?
Or are we completely on the wrong path?