Ivan Shmakov <[email protected]> wrote:
>>>>>> Igor Tandetnik writes:
>> Note that ending up in the IGNORE branch of INSERT OR IGNORE
>> statement doesn't constitute failure, but normal execution. The same
>> is true for INSERT ... SELECT statement where SELECT returns zero
>> rows so nothing is actually inserted.
>
> Thus was my question: how do I ensure that a SELECT … INSERT
> statement results in exactly one row being inserted?
In your hypothetical example of two INSERT statements in a trigger, that is
what would indeed happen, assuming both statements succeed. If either of the
two statements fails, the trigger as a whole would fail and any changes it made
rolled back.
> (Additional constraint: it should be possible to use the code in
> a trigger.) The best thing I was able to imagine is to replace:
>
> INSERT INTO "bar" ("foo")
> SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1;
>
> with:
>
> INSERT INTO "bar" ("foo")
> VALUES ((SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1));
If the nested SELECT returns an empty set, then this statement attempts to
insert NULL into bar.foo, which would fail due to NOT NULL constraint.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
