如何避免删除sql server 2008中事务表中使用的主表数据 [英] How to avoid deletion of master table data which is used in transaction table in sql server 2008

问题描述

大家好，



请帮助我，我是创建小型ERP应用程序，包含一些大师，如项目，单位，类别，项目类型每个都有他们的表和我我在我的交易中使用此表参考数据，如购买模块，其中包含项目名称，单位名称，类别名称，在我的购买查询表中保存其ID，但当我要删除主表格中的项目时，我将修改购买查询数据，它会显示一个错误，项目数据无法修改。我也使用外键限制，但我不会工作。





我的主要问题是如何显示用户不在交易表单中使用master时删除master的数据。

Hi Everybody,

please help me i am creation small ERP application that contains some masters like item,unit,category,item type each have their table and i am using this table reference data in my transaction like purchase module that contains item name,unit name,category name saving their ids in my purchase inquiry table but when i am going to delete an item in master table then i will modify the purchase inquiry data, it will shows me an error the data of item is not available for modification. i am also using the foreign key constraints but i will not work.


my main problem is to how to show user don't delete the data of master when it is used in transaction forms.

推荐答案

逻辑可能如下所示



在存储过程内，在主表上的 DELETE 操作之前，检查是否参考表 PurchaseInquiry 中有任何数据。



如下所示...（例如项目表格）

Logic may be like below

Inside the Stored Procedure, before the DELETE operation on Master Table, check whether there are any data inside the Referenced Table PurchaseInquiry.

Something like below... (example for Item Table)

IF EXISTS(SELECT col1 
          FROM PurchaseInquiry 
          WHERE ItemName = 'ItemNameWhichUserIsDeleting')
BEGIN
    -- Return a message that User can't delete the Item.
END
ELSE
BEGIN
    -- DELETE the Item.
END

在创建外出键约束时，您通常能够在主表行删除的情况下选择子表列值应该发生什么。我现在不记得SQLServer的情况，但是在MySql中你可以将动作设置为 RESTRICT 。这种方式在尝试删除主表上的一行时，如果在子表上存在引用它的行，则会抛出错误。这确保了数据库的一致性。



如果没有显示讨厌的错误消息，您可以修改代码，以便在继续删除/修改主表行之前进行检查。

When creating the foreing key constraints you're usually able to select what should happend on 'child' table column values in case of 'master' table row deletion. I don't recall now in the case of SQLServer, but in MySql you can set the action to RESTRICT. This way it throws an error when trying to delete one row on the master table if rows referencing it exist on the child tables. That ensures database consistency.

For not showing nasty error messages you may modify your code so you make the checks before proceeding to delete/modify the master table rows.

当两个表之间存在父子关系时，如果您尝试从master中删除数据并且它在子表中引用了数据，则sql server会生成错误，除非您使用CASCADE DELETE执行外键。那么你想阻止从Master表中删除数据还是想要做其他事情？

你可以显示来自try catch到ui的消息......

When you have parent child relationship between two tables sql server generate an error if you try to delete data from master and it has referenced data in child table unless you do the foreign key with CASCADE DELETE. So do you want to prevent the deletion of data from Master table or want to do something else?
and you can display the message from try catch to ui .....

这篇关于如何避免删除sql server 2008中事务表中使用的主表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！