Dear list, I'm probably doing something really stupid here, but I feel I need to ask you anyway to see id there is something that I've missed. Is it not possible for a trigger to trigger itself? I get triggers that trigger triggers, but so far not triggers that trigger themselves... (yes, many triggers.. :-) )
This is what I am doing: I need to compute the transitive closure of a graph: Tables I am using are: --- This table stores the connections--- CREATE TABLE level_level ( parent_id INTEGER REFERENCES levels(id), child_id INTEGER REFERENCES levels(id), PRIMARY KEY(parent_id,child_id) ); --- This table stores the TC CREATE TABLE tc_level_level ( ancestor_id INTEGER REFERENCES levels(id), descendant_id INTEGER REFERENCES levels(id), depth INTEGER, path TEXT, PRIMARY KEY(ancestor_id,descendant_id,depth) ON CONFLICT IGNORE ); Now, I have two triggers: --- This trigger inserts the simple connections into the TC table CREATE TRIGGER ll_tcll_insert after insert on level_level for each row begin delete from tc_level_level where depth > 1; insert into tc_level_level select NEW.parent_id ancestor_id,NEW.child_id descendant_id,1 depth,NEW.parent_id || ',' ||NEW.child_id path from level_level; end; --- This table stores the TC CREATE TRIGGER tcll_tcll_insert after insert on tc_level_level for each row begin insert into tc_level_level select tc1.ancestor_id, tc2.descendant_id, tc1.depth + tc2.depth,tc1.path || substr(tc2.path, length(tc2.ancestor_id)+1) from tc_level_level tc1, tc_level_level tc2 where tc1.descendant_id = tc2.ancestor_id; end; A sample run looks like this: > delete from level_level; > delete from tc_level_level; > INSERT INTO "level_level" VALUES(1,2); > INSERT INTO "level_level" VALUES(1,3); > INSERT INTO "level_level" VALUES(3,4); > INSERT INTO "level_level" VALUES(4,5); > INSERT INTO "level_level" VALUES(2,5); > select * from level_level; parent_id child_id ---------- ---------- 1 2 1 3 3 4 4 5 2 5 > select * from tc_level_level; ancestor_id descendant_id depth path ----------- ------------- ---------- ---------- 1 2 1 1,2 1 3 1 1,3 3 4 1 3,4 4 5 1 4,5 2 5 1 2,5 1 5 2 1,2,5 1 4 2 1,3,4 3 5 2 3,4,5 So, why is there not a 1 5 3 1,3,4,5 row? Second question is, of course, can I depend on this behaviour? Or, is it just because of the version of SQLite I am currently using (3.6.16)? Thanks! /Fredrik -- "Life is like a trumpet - if you don't put anything into it, you don't get anything out of it." _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
