0

I am using SQL Server 2008 Express R2 and I have a table that is self referencing, because I have a hierarchy struct.

I need to delete a root node, but I get an error because of foreign key. I have read that I can use two option, use a recursive CTE o use a instead of delete trigger.

Which is the difference brtween both of them? which is more efficient?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Álvaro García
  • 18,114
  • 30
  • 102
  • 193

1 Answers1

1

When you say use a delete trigger as opposed to a recursive CTE, I assume you are going to do some kind of loop in the trigger, which would mean the CTE would be more efficient.

For a CTE, try something like:

with cte as (
    select id as root, parent, id
    from [<YourTable>]
    where parent is null -- This selects root nodes

    union all

    select cte.root, d.parent, d.id
    from cte
    inner join data d on cte.id = d.parent
)
delete from [<YourTable>]
from [<YourTable>]
inner join cte on rel.id = cte.id
where cte.root = 1 -- This is the root to delete
muhmud
  • 4,474
  • 2
  • 15
  • 22
  • yes, this is what I understand as CTE, because the other option that I see is a instead of delete trigger, that has a different code, but I did not know if they were the same or not. – Álvaro García Apr 09 '13 at 08:59