Igor Tandetnik wrote:
> Can you show an example of a trigger that would make these statements
> work:
>
> insert into snapshot (bumf) values ('one');
> insert into sample(type, used) values ('a', 1);
> insert into sample(type, used) values ('b', 1);
>
> so that, at the end, the two new records in sample table would have
> snapshotid equal to snapshot.id from the record inserted into snapshot
> table.
>
Sure. I'll use your examples, just making sure to add an id insertion in
the first statement since the id column isn't specified as autoincrement.
sqlite> .headers on
sqlite> DROP TABLE IF EXISTS snapshot;
sqlite> CREATE TABLE snapshot(id INTEGER PRIMARY KEY, bumf TEXT);
sqlite> DROP TABLE IF EXISTS sample;
sqlite> CREATE TABLE sample(snapshotid INTEGER, type TEXT, used INTEGER);
sqlite> DROP TABLE IF EXISTS last_snapid;
sqlite> CREATE TABLE last_snapid(snapid INTEGER); -- Table will hold a
single row with the last inserted snapshot id
sqlite> INSERT INTO last_snapid(snapid) VALUES(NULL); -- Ensure the
(single) row exists, we'll keep updating it
sqlite> SELECT * FROM last_snapid;
snapid
sqlite> CREATE TRIGGER capture_last_snapid AFTER INSERT ON snapshot BEGIN
...> UPDATE last_snapid SET snapid = new.id WHERE rowid = 1;
...> END;
sqlite> CREATE TRIGGER update_sample AFTER INSERT ON sample BEGIN
...> UPDATE sample SET snapshotid = (SELECT snapid FROM last_snapid
WHERE rowid = 1) WHERE rowid = NEW.rowid;
...> END;
sqlite> INSERT INTO snapshot (id, bumf) VALUES (42, 'one');
sqlite> SELECT * FROM snapshot;
id|bumf
42|one
sqlite> SELECT * FROM last_snapid; -- To verify capture_last_snapid is
working
snapid
42
sqlite> INSERT INTO sample(type, used) VALUES ('a', 1);
sqlite> INSERT INTO sample(type, used) VALUES ('b', 1);
sqlite> SELECT * FROM sample;
snapshotid|type|used
42|a|1
42|b|1
sqlite> INSERT INTO snapshot (id, bumf) VALUES (999, 'another one');
sqlite> SELECT * FROM snapshot;
id|bumf
42|one
999|another one
sqlite> INSERT INTO sample(type, used) VALUES ('c', 3);
sqlite> INSERT INTO sample(type, used) VALUES ('d', 54);
sqlite> INSERT INTO sample(type, used) VALUES ('e', 168);
sqlite> SELECT * FROM sample;
snapshotid|type|used
42|a|1
42|b|1
999|c|3
999|d|54
999|e|168
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
