Mutating table problem

From: Jim Wolfe <wolfe_at_sra.com>
Date: Thu, 01 Mar 2001 21:22:22 GMT
Message-ID: <i4zn6.5627$%4.630098@newsread1.prod.itd.earthlink.net>

I understand what a mutating table is and why this particular situation is a mutating table problem. What I need is an idea on how to get around the problem. Here's the situation. I have a complex data structure that's used by a number of different primary tables. (Use a fixed space font like Courier to see the diagrams).
Logically, I have the following:

A -----

         |
         v
   B -> Structure
         ^
         |

C -----

Note that the structure table collects all instances of the given structure. The rows referenced from tables A, B, and C will be mutually exclusive. There is a one-to-many relationship from each of the primary tables to the Structure. Thus, the physical model introduces associative tables between each primary table and the structure:

A --> A Structure <-------

                           |
                           |
  B --> B Structure <-- Structure
                           |
                           |

C --> C Structure <-------

Each of the associative tables has an 'on delete cascade' dependency on its primary table. However, I'd also like to delete the rows in Structure. I can create triggers on each of the associative tables to delete the corresponding row(s) in Structure. This works fine if each of the primary tables were independent. However, the primary tables are dependent on a root table:

|----> A --> A Structure <-------

   |                               |
   |                               |
 root --> B --> B Structure <-- Structure
   |                               |
   |                               |

|----> C --> C Structure <-------

Each of the primary tables have an 'on delete cascade' dependency on the root. Now, if I delete a record in the root, the 'on delete cascade' dependencies in the primary tables will cause rows in A, B, and C to be deleted. These deletions will cause rows in the associative tables to be deleted (due to the 'on delete cascade dependency in the associative tables). The deletions in the associative tables will cause the triggers to fire which should delete the associated rows in the Structure table. However, since the first trigger to fire changes the Structure table, it is mutating for the subsequent triggers.

I'd appreciate ideas on getting around this problem. I could duplicate the Structure for each of the primary tables, replacing the associative table with a separate copy of the structure table. I don't like this idea since the structure is complex and I'd like to centralize its design. As I said, I can assert that the rows in Structure are mutually exclusive with respect to the primary/associative references. How might I use this to modify the triggers?
Thanks in advance,
Jim Wolfe

--

Received on Thu Mar 01 2001 - 15:22:22 CST