@louis :: SQL - Cascading Delete, or Delete Trigger, maintaining Referential Integrity - PLE

I am having great difficulty with cascading deletes, delete triggers and referential integrity.

The database is in First Normal Form.

I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

Table A

/ \

Table B Table C

\ /

Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.

SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.

Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.

When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?

This is an example of my delete trigger:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.

So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.

So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).

Hope this makes sense...

Thanks,

Josh

It is hard to advise how to impove abstract structure. No other answers that create all references with "do not enforce" clause and maintain all integrity by the triggers.|||

Yeah, the whole cascading thing can get confusing. Can you post a more complete sample? This trigger will not work as it is:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

You need to do something more like:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete B
FROM B
join deleted
on B.Akey = deleted.Akey

Delete C
FROM C
join deleted
on C.Akey = deleted.Akey
go

|||correct me if im wrong.... the triggers in sql server r 'after triggers' i.e after the delete operation in parent table is performed, only then will the trigger fire...now while deleting from parent table itself, it gives an error as at that time the reference is their.....sorry i havent tried it, but this is wat shud be hapenning .....|||

if u know the parent table and the primary keys refered by the child(fk) tables ... use queries to get rid of the child data first..somethin like

BEGIN TRANSACTION

delete from child1 where refered_column in (select pk_values from parent where value='i wanna delete' ) --nest to more levels if required...

select pk_values from parent where value='i wanna delete'

END

this may seem to be manual work but can be automized......

|||

Well, sure I can do it manually, but that is not really the question.

In SQL there are AFTER triggers, so one would assume that the ON trigger happens as, or before the delete, providing us with the ability to prevent the restraint errors within the database. Otherwise what is the point? I was always taught that there are things that the databae does well, and this is supposed to be one of those things: maintain referential integrity. However MS SQL seems to not be able to do one of the main things a database is supposed to do really well. Even access does this better.

Here is the revised trigger that actualy works:

CREATE TRIGGER [MeetingDelete]
ON [coordinateameeting].[tblMeetings]
FOR DELETE
AS
Delete from tblMeetingAttendees where MeetingID in (select ID from deleted)
Delete from tblMeetingLocations where MeetingID in (select ID from deleted)

However, it only works if I turn RI off completely. At that point I might as well just be using text files.

Does anyone have an explanation why I can only get the delete triggers to fire if I turn referential integrity off?

Thanks,

Josh

|||thats wat josh.... u need to turn off the RI if u want the trigger to work... as the trigger is AFTER , it will delete from the parent table, then the child table, but in the first statement itself it'll get an error msg for RI. had SQLSERVER had BEFORE trigger(no offence to MS but ORACLE has them) , this would have worked...it'll then delete from the child tables first before parent...so while deleting from parent there would be no error....evn with the RI on.