在删除记录之前检查规则违规 [英] Checking rule violations before deleting a record

问题描述

我想对我的SQL Server 2005数据库中的许多实体实施软删除方案。通过这种方式，我的意思是如果不存在参照完整性规则冲突，我想从表中删除一行，否则，我将在记录上设置一个标志以表示该行已被删除。我要强制执行此软删除模式的表必须将无操作作为插入/更新规范应用。

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".

我如何检查我要运行的删除是否违反外键约束？

我不想捕获异常-我想明确地检查看看规则是否会被违反。我也不想通过SELECT语句手动检查（维护噩梦）。我希望使用T-SQL解决方案；但是我使用的是Entity Framework，因此可以使用一种API（如果存在用于此任务的API）。

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.

推荐答案

与埃德·哈珀（Ed Harper）的解决方案类似，我也建议您使用INSTEAD OF DELETE TRIGGER，但是，我们的解决方案有所不同，因为我实际上建议您配置数据库以执行所需的完整性检查/规则。

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.

此实现可确保DBMS负责管理数据库完整性的全部责任，当然这是理想的方案。

This implementation ensures that the DBMS handles the entire responsibility of managing database integrity, which of course should be the desired scenario.

有道理吗？

这篇关于在删除记录之前检查规则违规的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！