Quoth Sergey Shishmintzev <[email protected]>, on 2010-09-05 21:57:24 +0300: > Hi, > I'm trying to simulate primitive table inheritance. > > There is my SQL code: > > -- 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;
Yukko---I wouldn't recommend trying to change a synthetic primary key of a row almost anytime at all, much less in an AFTER INSERT trigger! You might consider using views and then doing an INSTEAD OF INSERT trigger, something like: CREATE TRIGGER ... INSTEAD OF INSERT ON child1_view FOR EACH ROW BEGIN INSERT INTO base (bslots...) VALUES (bvalues...); INSERT INTO child1 (id, cslots...) VALUES ((SELECT last_insert_rowid()), cvalues...); END; and then probably suitable UPDATE and DELETE triggers too, presumably mechanically generated so you don't have to keep all the column names in sync manually. (This is the approach I sketched out for some multitable-inheritance ORM stuff that hasn't gotten much of anywhere yet.) ---> Drake Wilson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
