Search code examples
c#sql-server-2012delete-rowsqlexception

SQL Server - How to check a row can be deleteable before deleting

How can I check a row can be delete-able before deleting the row? It is like developing a new function CanDelete(RowID).

I have REFERENCE of this tables in another table for some of the rows.

One way I can do it is by catching the exception. Is there any better way?

Solution

  • Assuming your requirement is you do not want to delete a row is it is referenced to from another table.

    I would not leave this up to the c# code. What if another application is developed that does the delete or someone access the database directly.

    First you should set up a delete rule on the relationship . Secondarily you could write a trigger to check before deleting and Handel it in there. Finally you could write you delete statement with a not exist for example

    DELETE from TableA where ID in (select ID from tableA a left outer join tableB b on a.ID = b.ID where b.ID is NULL)