I suspect that this is wrong as nobody has suggested it but isn't this what triggers are meant to solve?
Staffan On Wed, Feb 18, 2015 at 2:13 PM, Simon Slavin <slavins at bigfraud.org> wrote: > > On 18 Feb 2015, at 11:38am, Flakheart <gary.flakheart at gmail.com> wrote: > > > I can deal with single table inserts but I think this would be some sort > of > > nested insert statement? > > There are no statements in SQL which can modify more than one table. So > you will need to use a number of commands, one for each table. > > However you should enclose all the commands in a transaction: > > BEGIN; > INSERT INTO tableA ...; > INSERT INTO tableB ...; > INSERT INTO tableC ...; > COMMIT; > > That way if any one of the commands is not executed because of an error, > none of the commands will be executed and you won't have half a recipe in > your system. From your schema it looks like you are going to have to > INSERT into your recipe table first, then find out what number was assigned > to recipe_id and use that when modifying the other tables. It's possible > to do all this within the same transaction. > > It might be a good idea to specify in your schema that all _id fields are > INTEGERs as follows: > > > recipe_instructions table recipe_id INTEGER ; > recipe_step INTEGER ; > step_description TEXT ; . > > By the way, it would be possible to use FOREIGN KEYs to ensure that, for > example, the 'recipe_id' column in your ingredients table only had genuine > recipe_id numbers in it. However, if you trust your software and your > programming it's not necessary to do this. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
