• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

How to ignore error caused by referential integrity?

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?
0
Vadim Rapp
Asked:
Vadim Rapp
  • 8
  • 4
  • 4
1 Solution
 
Scott PletcherSenior DBACommented:
That would take some very sophisticated code, because the referencing chains could theoretically be very long.  A --> B --> C --> D & E --> ...
0
 
Vadim RappAuthor Commented:
sure; but the server eventually either deletes, or not. Ideally, the solution would be not in doing the same work, but rather, ignoring the error. T-sql version of

on error resume next
delete from A where ...

In sql05, this probably could be accomplished by writing a .clr trigger, but this is sql2k.


0
 
Atlanta_MikeCommented:
You need an instead of delete trigger.

In the trigger you need something like this:

IF EXISTS(SELECT NULL FROM deleted del JOIN TableA a ON del.columnid = a.columnid JOIN TableB b ON a.columnid = b.columnid)
BEGIN
     RETURN

END
ELSE
BEGIN
    DELETE TableC from TableC JOIN deleted del ON c.columnid = del.columnid
END
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Vadim RappAuthor Commented:
To Atlanta_Mike : let me repeat:

"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."
0
 
Atlanta_MikeCommented:
You're right, I missed that. And Scott is right. The performance hit you would take trying to find the correct chioldren tables wouldn't be worth it at all.

Best answer is to add new code with each new table you add. The performance will be much better that way.
0
 
Vadim RappAuthor Commented:
it's not abandoned; but there was no satisfactory response. Please close without assigning any points.
0
 
Atlanta_MikeCommented:
vad, what kind of response are you looking for? What you are requesting would hose your system to try to follow a chain of children.

We don't care if we get point or not...but the fact is you stopped asking questions to lead you to the solution. If you had continued your questions there might have been an appropriate solution found.
0
 
Vadim RappAuthor Commented:
To: ScottPletcher

yes, but nobody said "it can't be done" - specifically, the question in the title: "How to ignore error caused by referential integrity?"

If there was such statement, presumably with a reference, I would immediately award the points. But the contributions of experts only restated what I said I already knew in the original question. And this is the main reason I don't want to award the points.

0
 
Vadim RappAuthor Commented:
thanks for reopening. So, to restate, is there a way to ignore errors caused by referential integrity?
0
 
Scott PletcherSenior DBACommented:
No.  If you define referential integrity to SQL Server, it will enforce it; after all, that's what you told it to do.
0
 
Vadim RappAuthor Commented:
It's understood that the integrity itself is always enforced; but is there a way to handle the consequences of that enforcement?

For example, inability to divide by zero is also always enforced; but in programming language (not t-sql), I can specify error handling, so the program will handle the situation gracefully. Is something like that possible in t-sql?

0
 
Scott PletcherSenior DBACommented:
Enforcing the constraint means preventing the triggering action from occurring *and raising an (untrappable, I think) error*.  So, I don't think you can really unenforce an error that's already been enforced.
0
 
Vadim RappAuthor Commented:
Do you think it would be possible in sql05 with clr trigger?
0
 
Atlanta_MikeCommented:
vad, its not possible to override the foreign key constraints you have created. It is better (not good) to enforce referential integrity using a trigger. When you start creating a huge process as you've asked for to manage your constraints.

The longer your trigger is running, trying to figure out what it should do the more you're going to block other processes required to run your app.

We all understand the want to automate a processes, but at some point it is counterproductive. If you want to minimize the amout you will have to write when you add to your schema, write a script that will generate the trigger. It will take a little work, no more than writing this behemoth of a trigger, but it will save your performance.

I can't tell you how many of my clients have called for help, when I've found their problems were related to a crazy trigger or some process that was where it shouldn't be.
0
 
Vadim RappAuthor Commented:
Again: even if it's counterproductive, it's already implemented in sql server. Whatever amount of work is involved, sql server does it every time before generating the error. I suspect that the way sql server does it, is programmed more effectively than any custom trigger doing the same - which is another reason not to do it.

The question is, is it possible to bypass/ignore/handle that error somehow.

Here's a very simple example.

begin transaction
delete from table1 where id=1
insert into table3 select 2
commit

Let's say the row with id=1 in table1 has childeren in another table2, so DELETE fails. However, the next INSERT runs and successfully inserts new row into table3, and commits.

But if you put the same in a trigger,

create trigger table3_i on table4 for insert as
delete from table1 where id=1
insert into table3 select 2

then INSERT does not run when delete fails.

So, if the error is untrappable, why sql server happily continues in a batch, even wrapped in a transaction, but can't do the same in a trigger?
0
 
Scott PletcherSenior DBACommented:
>> The question is, is it possible to bypass/ignore/handle that error somehow. <<

NO NO NO NO NO
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now