How to ignore error caused by referential integrity?
Posted on 2006-05-12
Table B has a foreign key related to the table A, so it's impossible to delete records from A that have "children" in B.
I want to write a trigger for the table C that would delete records from A if that is possible, i.e. if they don't have children in B. If they do have children, I don't want to delete them.
I could write the code that would check if the record in A has children in B and skip the deletion of so; but is it possible to do in general case, without hardcoding table names? That is, if tomorrow I relate to A new table D, I wouldn't have to adjust the code by teaching to look also in D as well.
I can put it in different way. The trigger oin C can try to delete the record from A without any checking. But if the deletion fails (because of the children in B), the trigger should not fail, thus the operation on C that triggered it should not be rolled back.
I understand I can write a code that for a given table would programmatically find out all relationships without cascade deletion, then check these relationships one by one using dynamic SQL. But maybe there's an easier way?