| Author |
Topic |
|
joliver
Starting Member
13 Posts |
 Posted - 2002-12-04 : 18:46:02
|
I have a question about how DRI and triggers interact.This is mostly a contrived example, but I'm trying to come up with a "best practice" for a situation I've run across several times trying to work with legacy / other people's code that I can't completely rewrite.Say the two tables are Customers and Contacts. Contacts belong to a Customer.Due to questionable design decisions, there's a "PrimaryContact" bitflag in the contact table. Whichever record has this flag cannot be allowed to be deleted, *except* when the related customer is also being deleted (via DRI currently).What's the best way to implement this sort of logic? Out of desperation, I tried the following trigger on the contacts table:If Exists (Select * From Deleted Where PrimaryContact = 1) and Exists (Select * From Customers, Deleted Where Customers.CustomerID = Deleted.CustomerID)BEGIN RAISERROR ('Stop trying to delete the primary contact', 16, 1) ROLLBACK TRANSACTIONENDHowever, this doesn't work, because the customer still exists in the scope of when this is running. I was able to construct an abomination of a query that used DBCC INPUTBUFFER to allow the delete to go through if the parent statement was a delete against the customer table, but I have no desire to use a hack of that magnitude, it was more of a "I wonder if I could..." thing.While we're on the subject of hacks, I might add that this issue is currently handled by a delete trigger on the customers table that removes the PrimaryContact flag on all of the related customers. You really do sometimes inherit the most bizarre code and database designs...Does anyone have a solution to this issue that I could use without being mortified when the next guy stumbles across it and thinks I'm a complete moron? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-05 : 07:01:12
|
| If the customer has already been deleted then the trigger should work (see below).I guess the problem is 'related customer is also being deleted (via DRI currently).'You can also implement this via a trigger and (if v2000) make sure that the check trigger fires last.should be this unless you guarantee only ever deleting a single customer.If Exists (Select * From Customers, Deleted Where Customers.CustomerID = Deleted.CustomerID and Deleted.PrimaryContact = 1)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
joliver
Starting Member
13 Posts |
Posted - 2002-12-05 : 09:18:05
|
| Hmm, I may have misstated the case somewhat.My problem is when the customer is deleted, it is supposed to cascade to all of the contacts via DRI. Currently, the trigger I have above (and I tried your revision) still kicks in whenever the customer is deleted.Short of a trigger that clears the primary contact field for all related contacts when a customer is deleted what's the best way to go about doing that? |
|
|
|
joliver
Starting Member
13 Posts |
Posted - 2002-12-05 : 09:46:12
|
| It works if I perform the cascaded delete by a trigger on customer deletion.Is this simply a matter of nested triggers being disabled? Or does it happen in a different order than the DRI cascade does? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-05 : 10:11:04
|
| This is just a guess about the way cascade is implemented - may be completely wrong. It could recognise a cacade delete and so change the order but I doubt it.Cascade deletes are performed to maintain referential integity.In your case the Contact must have a customer record.If the DRI deleted the Customer record before the Contact then it would give an error so it must delete the Contact records first. When the trigger fires on the Contact table the Customer is still there so gives an error.If it is all implemented in triggers then you have no problem as the Customer record will be deleted to trigger the cascade so will not be there when the Contract trigger fires.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
joliver
Starting Member
13 Posts |
Posted - 2002-12-05 : 10:42:51
|
| This is true, although I have to maintain all aspects of this relationship through triggers then, I can't even use DRI for inserts or the triggered delete of contacts fails if it's a FOR trigger, or hits the primary contact trigger if it's an INSTEAD OF trigger.This is acceptable, just annoying. I guess I can live with it.Thanks for your help, it's much appreciated. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-05 : 10:48:35
|
| from bolWhen a DELETE action to a child or referencing table is the result of a CASCADE on a DELETE from the parent table, and an INSTEAD OF trigger on DELETE is defined on that child table, the trigger is ignored and the DELETE action is executed.You could leave the DRI and turn your after triggers into instead of triggers to check the value. Looks like they won't have do the check for the cascade==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
joliver
Starting Member
13 Posts |
Posted - 2002-12-05 : 12:24:11
|
| Excellent! I must go try this immediately. |
|
|
|
joliver
Starting Member
13 Posts |
Posted - 2002-12-05 : 15:26:43
|
| Curses, foiled again.You can't have an INSTEAD OF DELETE trigger on a table that cascades deletes to another table via DRI.Back to the drawing board. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-05 : 20:43:41
|
| The instead of trigger goes on the child table so that the check isn't made as a result of the cascade.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
DRI and Triggers