Hi,
I'm trying to simulate primitive table inheritance.
There is my SQL code:
PRAGMA foreign_keys = ON;
CREATE TABLE base(
id INTEGER PRIMARY KEY,
n INTEGER
);
CREATE TABLE child1(
id INTEGER PRIMARY KEY REFERENCES base(id) DEFERRABLE INITIALLY DEFERRED,
t TEXT
);
-- when inserting row into table child1
-- new child1.id must be obtained from base table
CREATE TRIGGER insert_child1
AFTER INSERT ON child1 FOR EACH ROW BEGIN
INSERT INTO base(n) VALUES(-1);
UPDATE child1 SET id=last_insert_rowid() WHERE ROWID = NEW.ROWID;
END;
-- test case:
INSERT INTO base(n) VALUES(111111);
INSERT INTO base(n) VALUES(222222);
INSERT INTO base(n) VALUES(333333);
INSERT INTO child1(t) VALUES("child1/foo");
SELECT t FROM child1 WHERE ROWID = last_insert_rowid();
-- expected output: child1/foo
SELECT last_insert_rowid();
INSERT INTO base(n) VALUES(444444);
INSERT INTO base(n) VALUES(555555);
INSERT INTO child1(t) VALUES("child1/bar");
SELECT t FROM child1 WHERE ROWID = last_insert_rowid();
-- expected output: child1/bar
SELECT last_insert_rowid();
-- dump data
SELECT * FROM base;
SELECT * FROM child1;
Actual SQLite 3.7.2's output:
1
5
1|111111
2|222222
3|333333
4|-1
5|444444
6|555555
7|-1
4|child1/foo
7|child1/bar
I'm expecting something like:
child1/foo
4
child1/bar
7
1|111111
2|222222
3|333333
4|-1
5|444444
6|555555
7|-1
4|child1/foo
7|child1/bar
Is it possible to tracking updates of primary key in triggers and
returning expected last_insert_rowid() value in future releases?
Or please, suggest me how to fix my code.
Thanks.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
