I have set of related tables and hierarchical entities that include a same table FK reference (Id -- ParentId).

The structure looks like this:

List (1) --- (n) ListItem

Taxonomy (1) --- (n) TaxonomyEntry

Taxonomy --> List

TaxonomyEntry -->ListItem

I want to able to delete a TaxonomyEntry, and have all of the ListItem rows and "child" TaxonomyEntry rows delete automatically. The CASCADE takes care TaxonomyEntry-ListItem, but I still need to deal with the child entries.

If I try to create a constraint with cascading delete I get this error:

Msg 1785, Level 16, State 0, Line 1 Introducing FOREIGN KEY constraint 'FK_TaxonomyEntry_TaxonomyEntry' on table 'TaxonomyEntry' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Do I need to use a nested trigger for this? Anyone have any examples?

Hi Brian,

Please correct me if I'm wrong.

The way I got it was that TaxonomyEntry has a FK to Taxonomy and another FK to ListItem, right?

I didn't get how is TaxonomyEntry reference itself in this structure?

Also I understood that Taxonomy has a FK to List, and ListItem has a FK to List.