SQL Server 数据库 - 隐藏字段？
我正在 silverlight 应用程序上实现 CRUD，但是我不想以传统方式实现删除功能，而是想将数据设置为隐藏在数据库内。
有谁知道使用 SQL Server 数据库执行此操作的方法吗？
非常感谢帮助。
I'm implementing CRUD on my silverlight application, however I don't want to implement the Delete functionality in the traditional way, instead I'd like to set the data to be hidden instead inside the database.
Does anyone know of a way of doing this with an SQL Server Database?
Help greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您可以向“已删除”表添加另一列，其值为 0 或 1，并仅显示那些已删除 = 0 的记录。
您还可以创建仅包含未删除行的视图。
你的删除命令将如下所示：
You can add another column to the table "deleted" which has value 0 or 1, and display only those records with deleted = 0.
You can also create view which takes only undeleted rows.
And you delete command would look like this:
扩展 Lukasz 的想法，日期时间列也很有用。
这增加了位列无法做到的简单版本控制，这可能会更好
Extending Lukasz' idea, a datetime column is useful too.
This adds simple versioning that a bit column can not which may work better
在大多数情况下，我宁愿使用删除触发器将已删除的行归档到归档表中。这样我还可以捕获谁删除了每一行，并且删除的行不会影响我的性能。然后，当您想要包含已删除的表时，您可以创建一个视图将两个表合并在一起。
In most situations I would rather archive the deleted rows to an archive table with a delete trigger. This way I can also capture who deleted each row and the deleted rows don't impact my performance. You can then create a view that unions both tables together when you want to include the deleted ones.
您可以按照 Lukasz Lysik 建议 进行操作，并有一个服务字段作为“已删除”行的标志，当您不希望它们显示时将其过滤掉。我已经在许多应用程序中使用过它。
另一种建议是，如果存在预先存在的状态代码，则添加额外的状态分配。例如，在课堂出勤应用程序中，我们内部使用的出勤记录可以是“已导入”、“已注册”、“已完成”、“不完整”等* - 我们为出现无意重复的情况添加了“已删除”选项。这样我们就有了记录，而不仅仅是针对这个问题抛出一个新的专栏。
*这是幕后使用的数字代码的显示名称。只是澄清一下。 :)
You could do as Lukasz Lysik suggests, and have a field that serves as a flag for "deleted" rows, filtering them out when you don't want them showing up. I've used that in a number of applications.
An alternate suggestion would be to add an extra status assignment if there's a pre-existing status code. For example, in a class attendance app we use internally an attendance record could be "Imported", "Registered", "Completed", "Incomplete", etc.* - we added a "Deleted" option for times where there are unintentional duplicates. That way we have a record and we're not just throwing a new column at the problem.
*That is the display name for a numeric code used behind the scenes. Just clarifying. :)
使用触发器的解决方案
如果您是数据库触发器的朋友，那么您可能会考虑：
DeletedAt 和 DeletedBy列，Customer有一个CustomerView视图，它将过滤掉DeletedAt不为 null 的行（带有日期的 gbn 的想法列）Customer表上执行，而是在CustomerView上INSTEAD OF DELETE触发器来标记该行作为删除而不是物理删除它。如果您选择使用此模式，我可能会以不同的方式命名我的表，例如
TCustomer，并仅查看Customer以便客户端代码清晰。Solution with triggers
If you are friends with DB trigger, then you might consider:
DeletedAt and DeletedBycolumns to your tablesCustomerhave aCustomerViewview, which would filter out rows that haveDeletedAtnot null (idea of gbn with date columns)Customertable, but on theCustomerViewINSTEAD OF DELETEtrigger that would mark the row as delete instead of physically deleting it.I you choose to use this pattern, I would probably name my tables differently like
TCustomer,and views justCustomerfor clarity of client code.请小心这种实现，因为软删除会破坏引用完整性，并且您必须使用自定义逻辑在实体中强制执行完整性。
Be careful with this kind of implementation because soft deletes break referential integrity and you have to enforce integrity in your entities using custom logic.