Checking whether a Delete comes from a cascade

[ookete]

I am using a Delete Trigger on a table that is on the "many" side of a relationship. The relationship has "cascade delete" activated, and I would like to be able to tell in the trigger whether the delete is occuring as part of a cascade or not. Does anybody know how to do this?

Thanks!

 

[SQLSister]

I don't know of any direct way to do this. But here's a thought. I'm not guaranteeing this would work because I haven't ever tried anything like it. But put a trigger on the primary key table that writes "Cascade" to a field called DeleteSource in the foreign key table. Then when the record is deleted through the cascade, the delete trigger on the foreign key table can see if that field contains that phrase and do whatever it is you want to do in that case.

You probably will have to make it an INSTEAD OF trigger to make sure that the trigger happens before the cascading action. However, now that I look in BOL, it turns out that INSTEAD OF trggers cannot be defined if you have a cascading delete. You may have to do all the work including the cascade delete part in the INSTEAD OF trigger.

So the steps in the INSTEAD OF trigger would be:
- Update the foreign key table to indicate a cascade
- Delete the records from the foreign key table (which will trigger the foreign key delete trigger)
- Then delete the records from the main table (in an INSTEAD OF trigger, you need to actaully put in code to do the delete becasue an INSTEAD OF trigger is a process that takes places instead of the normal delete.)

Does this make sense or am I rambling again?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief