Click here to Skip to main content
15,072,267 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 ---------------------------------------------------
SQL
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'

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

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


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 will be the returned value by the stored procedure.

Thanks in advance,
Atul
Posted
Updated 22-May-12 9:42am
v2

1 solution

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[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900