[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?

[Daniel Polski]

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


-- For example:
UPDATE t1 SET value = 1 WHERE id = 1;

--Should make the trigger run:
INSERT INTO t3 VALUES(1, 'dummy');
INSERT INTO t4 VALUES(1, last_insert_rowid());

INSERT INTO t3 VALUES(2, 'dummy');
INSERT INTO t4 VALUES(2, last_insert_rowid());


How would you accomplish this?

Thank you!
/Daniel