在删除PK记录之前先跟踪FK记录 [英] Trace FK records before deleting a PK record

问题描述

我想创建一个存储过程，其中，
如果数据库中的任何其他表中都存在主记录，那么我想防止用户删除该主记录.
我以组织表中PK的OrgID为例.

- - 询问 - - - - - - - - - - - - - - - - - - - - - - - ------

Hi,
I want to create a stored procedure in which,
I want to prevent the user from deleting a Master record if its ID exist in any other table in the database.
I have taken example of OrgID which is PK in Organization table.

---- Query ---------------------------------------------------

Select so.name as Tablename from syscolumns sc
left join sysobjects so on sc.id = so.id  where so.xtype = 'U' and sc.name ='OrgID'

-------------------------------------------------- --------------

上面的查询为我提供了当前数据库中所有表的列表，其中包含OrgID字段，如下所示，

----------------------------------------------------------------

The above query gives me list of all the tables in the current database which contains OrgID field in it as under,

TableName
----------
M_Employee
M_Project
M_Customer
M_Supplier

现在，我想要的是增强上面的查询，以便提供给我总计记录在
前面的记录之后的记录总数 每个表.如果该数字大于零，则将确定OrgID记录存在于某处.

Now what i want is to enhance the above query so that it gives me total record count that comes after counting up the records in front of
each table. This number if greater than zero will determine that OrgID record exist somewhere.

----------------------------
|TableName     | count     |
----------------------------
| M_Employee   |    8      |
| M_Project    |    0      |
| M_Customer   |   12      |
| M_Supplier   |    0      |
|---------------------------
|                  20      |
|---------------------------

存储过程返回的值为20.

预先感谢，
Atul

20 will be the returned value by the stored procedure.

Thanks in advance,
Atul

推荐答案

如果您希望不删除具有子记录的父记录，则必须在外键上使用ON DELETE NO ACTION定义.请参见创建表 [

If you want that parent record isn''t deleted if it has child records, then you must use ON DELETE NO ACTION definition on the foreign key. See CREATE TABLE[^]

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