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.
Am I missing something obvious? If not, does anyone have any brilliant ideas? John Elrick Fenestra Technologies Example...it can be assumed that the actual hierarchy will run from 0..n children for any given node. CREATE TABLE FOO ( PARENT_ID INTEGER, CHILD_ID INTEGER ); CREATE TRIGGER FOO_AD1 AFTER DELETE ON FOO BEGIN DELETE FROM FOO WHERE PARENT_ID = old.CHILD_ID; END; INSERT INTO FOO VALUES (1,2); INSERT INTO FOO VALUES (2,3); INSERT INTO FOO VALUES (2,4); INSERT INTO FOO VALUES (3,5); COMMIT; SELECT * FROM FOO => [["PARENT_ID", "CHILD_ID"], ["1", "2"], ["2", "3"], ["2", "4"], ["3", "5"]] DELETE FROM FOO WHERE PARENT_ID = 1 The result SELECT * FROM FOO => [["PARENT_ID", "CHILD_ID"], ["3", "5"]] The desired result SELECT * FROM FOO => [["PARENT_ID", "CHILD_ID"]] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
