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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users