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. > (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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users