Delete trigger for foreign keys??????

[pirate007]

Hello,

i am stuck up witha funny problem. I have a table say A with a primary key - say A_ID. I have another table B with fields B_ID, B_Name and BA_ID -> references A(A_ID). I now set atrigger on table A FOR DELETE to delete all the corresponding rows in table B. This however gives me a constraint violation error. Can anybody please help me out??

 

[TomSark]

If I understand your question correctly, you are most likely violating referential integrity. The rules for RI as as follows:

You cannot add a column to a referencing column (FK) without a valid referenced column (PK).

You cannot remove a referenced column (PK) if it contains refencing columns (FKs).

One way to get around your problem would be to remove the RI constraints you have implemented in the table definitions and implement the customized RI rules in triggers.

For example, drop the FOREIGN KEY constraints, and code the customized RI contraint rules in the triggers (in INSERT, UPDATE, and DELETE triggers), and add the cascading delete logic to the DELETE trigger.

Hope this helps... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[smasaitis]

pirate007,
I had the same problem until I found help on Microsoft's web site (I don't have the link). Here is the bottom line: you cannot have constraints on tables and try to use a delete trigger. Triggers are executed first before the constraint is checked. This is why you get the error.

Now you could delete the constraints and use the trigger like Tom Davis suggested. However, I did not choose this technique. Although triggers are helpful, I wanted to keep the constraints on the tables. Instead, I think the best technique is to use stored procedures to perform the deletion. Delete all the children first, then delete the parent. Something like this...

CREATE PROCEDURE SP_Delete AS

@ID INT

DELETE FROM B WHERE BA_ID = @ID
DELETE FROM A WHERE A_ID = @ID


Good luck,
Steve