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
>

Reply via email to