CODEWITHSUNDEEP
sqlsql-serverdatabaserelationship
CAD
CAD

Reputation: 4312

Maintaining consistency when doing logical delete

I'm performing logical delete when an item should be deleted from the database.

I have added an additional DateTime column to every table that we need to perform logical delete. So when deleting you just update the field like...

UPDATE Client 
SET deleted = GETDATE() 
WHERE Client.CID = @cid

Later if it should be recovered then...

UPDATE Client 
SET deleted = NULL 
WHERE Client.CID = @cid

So that a typical selection statement would look like...

SELECT * 
FROM client 
WHERE CID = @cid AND deleted IS NULL

But the problem is how to handle dependencies to maintain the consistency of the database in this approach. For ex. before deleting (actually updating) an employee I have to do several checks such as to see whether there are any related attendance / Bank Accounts / Wages data/ History etc. in related tables pertaining to the employee being deleted.

So that what's the normal practice in doing such things? Do I need to check every thing in 

IF EXISTS (SELECT...)

statements?

EDIT:

If I want to prevent the update when it has related records I could do something like this using UNION...

IF NOT EXISTS (SELECT emp_id FROM BankAccount WHERE emp_id = '100' UNION SELECT EID FROM Attendance WHERE EID = '100' UNION SELECT employee_id FROM SalaryTrans WHERE employee_id = '100')
UPDATE Employee SET Employee.deleted = GETDATE() WHERE emp_id = '100'

Would this be a acceptable solution?

Upvotes: 0

Views: 589

Answers (2)

Robert Sheahan
Robert Sheahan

Reputation: 2100

Look into triggers, they might be helpful here.

You could define a trigger on your employee table that checked to see if your logical delete would cause problems for other tables. It involves manually keeping track of what tables need access to employees, so it isn't as robust as allowing foreign key constraints to track that for you, but it can work. I'd set it up as an "AFTER UPDATE" trigger and roll back the transaction (within the trigger) if it found another table referencing the employee. They'd get a rollback anyway if they tried to actually delete an employee used in a FK constraint, so that's not that different.

Another approach is to use an AFTER DELETE trigger to copy deleted employees to a "deleted_employees" table, that way you're still hanging on to them, but any tables that reference that employee via FK will error and roll back the transaction before your trigger even has a chance to run.

I have to use a similar logic to what you proposed (just check every time you use it) in some of my stuff, and mostly I include a bit field "IsDead" that I set when I kill a record, and then I have to reference that EVERY time I use the table. But I mostly build views because my schema is complex, and it's trivial to include the IsDead = 0 in the where clause of the view. I don't know how IsDead = 0 would compare to DelDate IS NULL, if you have a large database you might test that out.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

But the problem is how to handle dependencies to maintain the consistency of the database in this approach. For ex. before deleting (actually updating) an employee I have to do several checks such as to see whether there are any related attendance / Bank Accounts / Wages data/ History etc. in related tables pertaining to the employee being deleted.

So that what's the normal practice in doing such things?

It depends entirely on your application.

Some companies might require all the pending wages, accumulated vacation days and sick days, etc., to be "handled" before deleting a person. Handled might mean converting all those things to money, which is added to a final paycheck. Other companies might allow deleting at any time, knowing that a logical delete doesn't affect any of the related rows in other tables. Application code would be expected to know how to deal with cutting a final check to a deleted person.

Other applications might not deal with anything as important as wages and taxes. They might allow a logical delete at any time, and just not worry about the trivial consequences.

Upvotes: 1

Related Questions