删除记录前检查规则违规情况
我想为我的 SQL Server 2005 数据库中的许多实体实施“软删除”方案。 我的意思是，如果没有违反引用完整性规则，我想从表中删除一行，否则我将在记录上设置一个标志来表示它已被删除。 我希望强制执行此“软删除”模式的表必须将“无操作”应用为“插入/更新规范”。
如何检查我想要运行的删除操作是否会违反外键约束？
我不想捕获异常 - 我想明确检查是否会违反规则。 我也不想通过 SELECT 语句手动检查（维护噩梦）。 我更喜欢 T-SQL 的解决方案； 但我使用的是实体框架，因此如果存在用于此任务的 API，则可以使用 API。
请注意，此处提出了类似的问题，但给出的答案不符合我的要求。
I would like to implement a "soft-delete" scheme for a number of entities in my SQL Server 2005 database. By this, I mean I would like to delete an row from a table if there are no referential integrity rule violations, otherwise I will set a flag on the record to signify it has been deleted. The table I wish to enforce this "soft-delete" pattern must have "No Action" applied as the "Insert/Update Specification".
How can I check to see if the delete I want to run will violate foreign key constraints?
I do not want to capture exceptions - I would like to explicitly check to see if rules would be violated. I also do not want to have to manually check via SELECT statements (maintenance nightmare). I would prefer a solution in T-SQL; but I am using Entity Framework, so it would be possible to utilize an API if one exists for this task.
Note that there is a similar question stated here, but the answers presented do not suit my requirements.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
与 Ed Harper 的解决方案类似，我也建议您使用 INSTEAD OF DELETE TRIGGER，但是，我们的解决方案有所不同，因为我建议您实际配置数据库以强制执行所需的完整性检查/规则。
这样，当删除操作由于违反约束而无法在触发器代码中成功完成时，您可以标记记录（“软删除”）而不是实际删除它。
或者，如果没有发生违规，删除操作将成功完成。
此实现确保 DBMS 承担管理数据库完整性的全部责任，这当然应该是所需的场景。
合理？
Similar to Ed Harper’s solution, I would also suggest that you use an INSTEAD OF DELETE TRIGGER however, we differ in our solution in that I propose you actually configure your database to enforce the desired integrity checking/rules.
This way, when a delete operation cannot complete successfully within your Trigger code, due to constraint violations, you can mark the record (“soft delete”) rather than actually delete it.
Alternatively, should no violations occur, the delete operation will simply complete successfully.
This implementation ensures that the DBMS handles the entire responsibility of managing database integrity, which of course should be the desired scenario.
Make sense?
您可以在目标表上使用 INSTEAD OF DELETE 触发器来运行select 语句来检查 FK 违规情况，如果没有发现，则在执行删除之前。 这将使您能够封装数据库中的所有逻辑，但如果您有大量删除、大型数据集或许多外键，性能可能会成为问题。 如果您认为这会带来不可接受的维护问题，则可以编写使用数据库元数据动态构建必要查询的通用代码，而不是手动编码所有内容。
另一种可能更简单的选择是对所有删除实施软删除，然后包括计划的维护任务，该任务将所有可以安全删除的软删除转换为硬删除。 不执行计划的硬删除也是可以接受的，具体取决于数据集的大小和删除的数量。
编辑
John Sansom 的答案是更好地实现触发器解决方案。
You could use an INSTEAD OF DELETE trigger on the target tables to run select statements to check for FK violations before carrying out the delete if none are found. This will enable you to encapsulate all the logic in the database, although performance may be a problem if you have a lot of deletes, a large dataset or many foreign keys. It would be possible to write generic code which uses the database metadata to build the necessary queries dynamically, rather than coding everything by hand, if you feel this presents an unacceptable maintenance problem.
The other, perhaps simpler, option would be to implement soft delete for all deletes, and then to include a scheduled maintenance task which converts all the soft deletes which can be safely removed into hard deletes. It may be acceptable not to carry out the scheduled hard deletions, depending on the size of the dataset and the number of deletes.
EDIT
John Sansom's answer is a better implementation of the trigger solution.