Если я правильно понял, то стоит задача удалить поддерево, если удаляется узел дерева, причем глубина поддерева заранее неизвестна. Сам решал подобную задачу. Ниже привожу варианты решения в той последовательности, в которой сам их опробовал, указывая их плюсы и минусы.

Вариант 1. Устанавливаешь в свойствах БД возможность запуска рекурсивных триггеров. В триггере на удаление из таблицы MyTable пишешь примерно следующее:

Рекурсивный запуск подобного триггера автоматом уничтожит все поддерево. Только не забудь, что в самом начале триггера должно стоять условие выхода из рекурсии (то есть, оператор if, а не что-либо другое), иначе получишь бесконечную рекурсию с сообщением об ошибке превышения максимального количества уровней рекурсивных вызовов. Плюсы такого решения - простота реализации. Минусов гораздо больше. Как только включили возможность запуска рекурсивных триггеров, появляется вероятность возникновения непреднамеренной рекурсии по совокупностям таблиц с триггерами. Придется более тщательно продумывать скрипты всех остальных триггеров, и в некоторых из них исключать нежелаемый повторный запуск принудительной проверкой TRIGGER_NESTLEVEL в самом начале триггера. Это минус номер раз. Минус номер два заключается в низком (по сравнению с другими методами) быстродействием. Снижение быстродействия в основном связано с несколькими негативными моментами. Во-первых, на вычисление условия выхода из рекурсии уходит дополнительное время. Во-вторых, при каждом рекурсивном вызове происходит сохранение контекста (это делает SQL-сервер незримо для нас, но он это делает, и на это уходит приличное количество ресурсов). Во-третьих, на практике обычно в триггеры включается более сложный алгоритм дейтсвий, нежели приведен выше. Если текст триггера емкий - а у меня именно так - и одновременно наличествуют причины, по которым производится перекомпиляция триггера при каждом его запуске (перечисление этих причин - отдельная тема) - а у меня они тоже имеются, то получается, что при уделении поддерева глубиной 10 уровней текст триггера должен перекомпилироваться 10 раз! Результат - работает все это хозяйство не просто тормознуто, а супер-пупер-тормознуто. Поэтому я от такого решения отказался.

Вариант 2. В НЕ рекурсивном триггере на циклах устраивается псевдорекурсивный алгоритм раскраски поддерева с помещением всех выявленных им идентификаторов во временную таблицу или табличную переменную. Когда алгоритм отработает, запускается ОДИН РАЗ delete from..., который удаляет из нужной таблицы записи с вычисленными идентификаторами. Этот вариант уже работает существенно быстрее, но для моего случая и его быстродейтсвие меня не устроило.

Вариант 3. Можно существенно увеличить скорость подобной операции, если циклы вообще не крутить, а на этапе ввода новых записей в таблицу привязывать к каждой записи (с помощью триггера на вставку) схему ее расположения в дереве - то есть, полный путь, начиная от вершины, а не только ссылку на прямого родителя. а) Полный путь можно хранить в виде поля varbinary, в которое помещены сцепленный по порядку слева-направо идентификаторы всех родительских записей. б) Можно полный путь хранить в специальной таблице, в которой заводятся специальные поля - уровень иерархии (или порядковый номер идентификатора в прописываемом пути) и собственно сами идентификаторы. Вообще вариант 3 - что а), что б) - имеет один минус. Его реализаций требует выделения под структуру, в которой хранятся "древесные" пути много места, что может привести к распуханию БД. При этом а) требует пространства меньше, чем б). Если база просто гигантская, и увеличение ее размера крайне нежелательно, то можно ограничиться вариантом а). Если размер БД существенного значения не имеет, для более высокого быстродействия предпочтительнее вариант б). Разница между а) и б) состоит в том, что по варианту а) в любом случае будет отрабатывать, да, всего лишь один (по сравнению с вариантом 1 и 2), но гарантирован table scan, поскольку поддерево отфильтровывается по вычисляемому условию (сравнение подстрок varbinary). Тут никакие индексы заведомо работать не будут. Вараинт б) позволяет завести индексы на иденификаторы вспомогательной таблицы, и их на самом деле можно будет задействовать. Но объем БД вырастет прилично - во-первых, из-за весьма емкой вспомогательной таблицы (число записей которой очень грубо равно квадрату числа записей основной таблицы, поделенному на два), а во-вторых, из-за таких же гигантских индексов по полям этой вспомогательной таблицы.

Как конкретно решать эту задачу и решать ли ее вообще, каждый думает сам.