Hello again, I think I can simplify the actual question: Can I use a SELECT returning multiple rows as input to some INSERT logic, and in that logic INSERT into multiple tables?
Like if I have a table t1 containing id 1, 2, 3. For each id in t1, do INSERT INTO t2 (...) INSERT INTO t3 (...) --But this depends on the last_insert_rowid() from the insert into t2 above The problem for me is that I don't know how to fetch the last_insert_rowid() needed for the t3 table. (If I didn't have to split up the insert I could simply run something like INSERT INTO t2 SELECT id FROM t1; ) Best Regards, Daniel Den 2015-09-24 kl. 13:31, skrev Marco: > Excerpts from Daniel Polski's message of 2015-09-24 07:38:24 -0300: >> Hello! >> >> --Table t1 contains some "base" data >> CREATE TABLE t1 ( >> id INTEGER PRIMARY KEY, >> name VARCHAR(10), >> value INT NOT NULL >> ); >> >> INSERT INTO t1 VALUES(1, 't1 1', 0); >> INSERT INTO t1 VALUES(2, 't1 2', 0); >> >> --Table t2 can contain extra data for a row in table t1 >> CREATE TABLE t2( >> id INTEGER PRIMARY KEY, >> t1_id INT NOT NULL, >> data INT NOT NULL, >> CONSTRAINT 'FK_t2_001' FOREIGN KEY( t1_id ) REFERENCES t1 ( id ) >> ); >> INSERT INTO t2 VALUES(1, 1, 10); >> INSERT INTO t2 VALUES(2, 1, 20); >> INSERT INTO t2 VALUES(3, 2, 30); >> >> CREATE TABLE t3 ( >> id INTEGER PRIMARY KEY, >> name VARCHAR(10) >> ); >> >> CREATE TABLE t4( >> t2_id INT NOT NULL, >> t3_id INT NOT NULL, >> CONSTRAINT 'FK_t4_001' FOREIGN KEY( t2_id ) REFERENCES t2 ( id ), >> CONSTRAINT 'FK_t4_002' FOREIGN KEY( t3_id ) REFERENCES t3 ( id ) >> ); >> >> >> >> -- Here is where my real question starts. Can I fetch a list of rows in >> a trigger >> -- and update different tables from that data? Im looking for some kind >> -- of looping over the rows found in t2, where I can insert data to t3 and >> -- t4 for each row found in t2. >> >> CREATE TRIGGER trigger_1 AFTER UPDATE OF value ON t1 >> WHEN new.value = 1 >> AND EXISTS (SELECT id FROM t2 WHERE t1_id = new.id) >> AND (some more rules) >> ... >> -- For each row in t2 with a t1_id referencing the t1 id, >> -- 1: insert a row into t3 >> -- 2: insert a row into t4 binding the newly created row in t3 to the >> corresponding row in t2 > may try this to insert into t3. > > CREATE TRIGGER trigger_on_table_1 > AFTER UPDATE OF value ON t1 > FOR EACH ROW WHEN NEW.VALUE = 1 AND EXISTS (SELECT id FROM te WHERE > ti_id = NEW.id) > BEGIN > > -- SELECT THE ROWS FROM t2 to insert values > INSERT INTO t3 VALUES(id, name) SELECT t2.t1_id, 'dummy' FROM t2 > WHERE t2.t1_id == NEW.id; > > END; > > Then you may wish to set a trigger to t4 itself to handle the newly > created t3 rows. >
