Re: Hierarchical Deletion via a Trigger?

> On Fri, Feb 22, 2008 at 10:46:00AM -0500, Igor Tandetnik wrote:
>  
>> John Elrick <john.elrick@...> wrote:
>>    
>>> I'm a bit stumped and was curious if anyone had an elegant solution
>>> for this problem.  Assuming the following simplified example, my goal
>>> is to cascade the deletes until all parent/child relations have been
>>> deleted. The trigger removes the first level, but stops there (I
>>> believe this behavior is documented).  I can think of a delete query
>>> which would also remove the first level, but am having a brain lock
>>> on any single query which would walk a chain of arbitrary length.
>>>      
>> It's impossible in pure SQL, unless the DBMS supports special syntax for
>> recursive queries, and/or recursive triggers. SQLite supports neither.
>> You would have to code the recursion in your host application.
>>    
>
> IIUC ANSI SQL has a WITH keyword and support for recursive queries.
>
> SQLite doesn't support this, of course.  But it could (whether it will
> is another story).
>