Igor Tandetnik wrote:
>> 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.
>>
>
> But it _is_ specified as INTEGER PRIMARY KEY.
In your example it wasn't. It also wasn't in Martin's original table
creation statement.
> If I'm inserting a
> specific Id into snapshot, I'm just going to explicily specify the same
> id when inserting into sample, and I wouldn't need all the song and
> dance you show. The whole issue is that the ID in snapshot table is in
> fact automatically generated.
>
>
It makes absolutely no difference. I just noted that so you wouldn't
think it relied on a specific ID.
Here's the whole thing again - I updated the table creation on the
snapshot table and removed the id from the insertions on it. The
triggers still work perfectly.
sqlite> .headers on
sqlite> DROP TABLE IF EXISTS snapshot;
sqlite> CREATE TABLE snapshot(id INTEGER PRIMARY KEY AUTOINCREMENT, 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 (bumf) VALUES ('one');
sqlite> SELECT * FROM snapshot;
id|bumf
1|one
sqlite> SELECT * FROM last_snapid; -- To verify capture_last_snapid is
working
snapid
1
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
1|a|1
1|b|1
sqlite> INSERT INTO snapshot (bumf) VALUES ('another one');
sqlite> SELECT * FROM snapshot;
id|bumf
1|one
2|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
1|a|1
1|b|1
2|c|3
2|d|54
2|e|168
On a sidenote: Are we nitpicking without even checking our facts, or are
we trying to help someone?
> For more details on the relationship between INTEGER PRIMARY KEY and
> AUTOINCREMENT, see http://sqlite.org/autoinc.html
>
I'm very familiar with it, but thanks for your concern.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
