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.
--
Marco Arthur @ (M)arco Creatives
