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
