>>>>> Igor Tandetnik writes:
>>>>> Ivan Shmakov <oneing...@gmail.com> 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.
Yes.
The point is that, thanks to OR IGNORE, the first one succeeds
even if the record being INSERT'ed already exists in the table.
(The ON CONFLICT clause is non-standard, and I was initially
reluctant to use it in my code. However, the example being
discussed has made its benefits obvious to me.)
>> (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.
… Exactly as intended.
--
FSF associate member #7257
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
