You basically have this structure: MOVIES movieid, title
PEOPLE peopleid, name MOVIEPEOPLE movieid, peopleid, role You insert a row into MOVIES and then insert one or more rows into PEOPLE and then you want to associate people with a movie in your MOVIESPEOPLE table. In order to do this, you must store the new movieid in a variable and the peopleid(s) in a variable (or in an array if there's more than one) so that you have them on hand when you are inserting rows into MOVIEPEOPLE. This is a procedural task and yu have to do it in a program. You cannot populate MOVIEPEOPLE by using a trigger on MOVIES or by using a trigger on PEOPLE. Regards Tim Romano Yuzem wrote: > Tim Romano wrote: > >> You should keep your id and the imdbid in separate columns, because you >> can then insert a title even if IMDB does not have it yet. >> >> > > > I have this tables: > CREATE TABLE movies(id integer,imb_id integer,title > text,unique(imdb_id),PRIMARY KEY (id)) > CREATE TABLE directors(id integer,name_id integer,unique(id,name_id)) > CREATE TABLE names(name_id integer,name text,bio > text,unique(imdb_name_id),PRIMARY KEY (name_id)) > > The table movies holds info about movies and the table names holds info > about persons, the table directors relate both tables. > > The problem is that to insert something in the table directors I must use > last_insert_rowid() after inserting the movie to get the rowid of the movie > but I also need the rowid for the director. > If I insert in names first I can't get the movie id and vice-versa. > > I need something like: last_insert_rowid(movies) and > last_insert_rowid(names) > > Any idea on how to solve this problem? > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
