IsDeleted位字段的低级过滤 [英] Low-level filtering of IsDeleted bit field

问题描述

从不实际删除记录可以提供额外的数据保护层.

一种常见的方法是在每个数据库表上都有一个IsDeleted标志，以指定是否删除"记录.但是，如果没有适当的低级机制来过滤掉记录，错误可能会开始侵入应用程序空间(而且它们并不总是很容易发现！).

就我而言，我编写了自己的Entity类来处理CRUD操作.这些类中的SELECT方法会自动对IsDeleted进行过滤，并且这些类是由我编写的工具自动生成的，以确保最大程度地减少此问题.但是，我知道有些人可能希望使用其他技术来解决此问题.

您更喜欢哪种技术? Microsoft SQL Server是否提供一种低级机制，使所有信息流经该机制，从而允许修改传入的SELECT语句，以便在处理该语句之前添加适当的筛选?

如果SQL Server提供了SELECT语句的对象模型，可以对引用表进行迭代，并且可以在每个表上附加一个[AND] IsNull(IsDeleted，0)= 0表达式，那就太好了. h2_lin>解决方案

SQL Server不能立即提供此功能.首先，这是一种蛮力方法，您最终将不得不弄清楚是否存在where语句，如果没有则添加它，并且您只需要将其应用于某些对象即可.桌子.从表面上看，这似乎是一个有吸引力的选择，但实际上，它会引起问题.在那些要检查已删除记录的情况下又怎么办呢?最后您遇到了IsNull(IsDeleted, 1) = 1 AND IsNull(IsDeleted, 0) = 0?

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.

解决方案

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?

这篇关于IsDeleted位字段的低级过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！