Low-level filtering of IsDeleted bit field

Question

4.00/5 (1 vote)

See more:

SQL-server-2000

SQL-Server-2005

, +

Never actually deleting records can provide an extra layer of data protection.

A common approach is to have an IsDeleted flag on each database table to specify whether the record is "deleted" or not. However, without the proper low-level mechanism in place to filter out the records, bugs can begin to invade the application space (and they're not always easy to spot!).

In my case, I have written my own Entity classes to handle CRUD operations. The SELECT methods within these classes automatically filter on IsDeleted and the classes are auto-generated by a tool I wrote to ensure this issue is minimized. However, I recognize some folks may want to use other techniques to address this issue.

What technique(s) do you prefer? Does Microsoft SQL Server provide a low-level mechanism that everything flows through that permits the modification of incoming SELECT statements to add the appropriate filtering prior to the statement being processed?

It'd be awesome if SQL Server provided an object model of the SELECT statement where the reference Tables could be iterated through and you could append an [AND] IsNull(IsDeleted, 0) = 0 expression to each.

Posted 30-Jun-11 6:07am

army_man71655

Updated 30-Jun-11 6:13am

Simon_Whale

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Accepted Answer · 2011-06-30T08:13:00

Solution 1

SQL Server does not provide this functionality out of the box. For a start, it's very much a brute force method where you would end up having to work out whether or not there was a where statement present and add it if not, and you'd have to apply it only to certain tables. On the surface, it appears that it would be an attractive option but, in reality, it would cause problems. And what about those cases where you want to check for deleted records, so you end up having IsNull(IsDeleted, 1) = 1 AND IsNull(IsDeleted, 0) = 0?

Posted 30-Jun-11 8:13am

Pete O'Hanlon

Comments

army_man71655 2-Jul-11 9:16am

Thanks Pete,

I guess no one else is going to respond. Your answer was swell however I was hoping this might stimulate some dialogue. Better luck next time. Thanks for your input!

Pete O'Hanlon 3-Jul-11 16:56pm

Why not raise this in the lounge? Make it clear you're talking conceptual here and see what interest you can provoke there.

army_man71655 4-Jul-11 17:35pm

I'll see about doing that. Thanks for the suggestion, Pete.