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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
