nested sql statements

Question

Hello, I've a self join table when I delete or update it's id I want to delete or update all the direct and indirect affected records SQL server does not allow this type of cycle cascading I've decided to use triggers but this triggers will file recursively and they will be terminated at 34 level and I don't know the depth of records and event I disable the trigger and re enable it after completing the process how can I construct a SQL statement that achieve this logic?

Answer 1

You should use a construct like nested sets for that kind of data: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Edit: Ok, with circular references you'll have a problem... But dependent on your data it still may help?

Answer 2

Because your table has circular references, a regular SQL statement won't suffice. Instead, you could write SQL using the following recipe:

1. create a temporary table for the IDs you want to process.
2. Create an SQL query that inserts the referenced IDs into the temp table. Make sure you do not insert duplicates.
3. Put the query in a loop and use counters to determine if records have been added. If no extra records were added, exit the loop.
4. Create your update statement that uses the IDs in the temp table.
5. Drop the temp table.

Oh, and try to improve your accept rate by accepting some answers to your previous questions. That might explain the downvotes. ;-)