On Sun, 20 Jan 2008 19:16:03 -0800 (PST), Ken wrote > Jay Sprenkle <[EMAIL PROTECTED]> wrote: >> I'm deleting a tree of data stored in sqlite and was looking for the >> most efficient way to do it. > > You'll need one trigger per table to cause the delete to cascade > through the tree.
Watch out. SQLite doesn't support recursive triggers, so the following won't work. At least, it didn't work for me. :^) SQLite version 3.5.4 Enter ".help" for instructions sqlite> create table x (id, parent); sqlite> create trigger deltree after delete on x begin ...> delete from x where parent = old.id; ...> end; sqlite> insert into x values(0, null); sqlite> insert into x values(1, 0); sqlite> insert into x values(2, 0); sqlite> insert into x values(3, 1); sqlite> delete from x where id = 0; sqlite> select * from x; 3|1 sqlite> In response to the deletion of (0,null), the trigger fired, deleting (1,0), and (2,0). But the trigger didn't fire again in response to either of these subsequent deletions, so (3,1) was not automatically deleted. If anyone knows how to get around this problem, I would like to know. -- Andy Goth <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
