sql server - is the same recursive CTE and trigger instead of delete? -

i using sql server 2008 express r2 , have table self referencing, because have hierarchy struct.

i need delete root node, error because of foreign key. have read can use 2 option, use recursive cte o use instead of delete trigger.

which difference brtween both of them? more efficient?

thanks.

when use delete trigger opposed recursive cte, assume going kind of loop in trigger, mean cte more efficient.

for cte, try like:

with cte (     select id root, parent, id     [<yourtable>]     parent null -- selects root nodes      union      select cte.root, d.parent, d.id     cte     inner join data d on cte.id = d.parent ) delete [<yourtable>] [<yourtable>] inner join cte on rel.id = cte.id cte.root = 1 -- root delete