insert into view returning id returns null
(1) By happy-barney on 2021-07-24 14:35:37 [link]
I'm playing with insertable views and returning clause Schema create table foo (id integer primary key, name text); create view bar as select * from foo; create trigger bar_insert instead of insert on bar for each row begin insert into foo (name) values (new.name); end ; When executing insert into view (using snapshot - SQLite version 3.37.0 2021-07-19 14:00:29) insert into bar (name) values ('a') returning id; I get one row with one column, but its value is NULL. Modification of data is successful otherwise, even when inserting into multiple tables.
(2) By Simon Slavin (slavin) on 2021-07-25 12:42:58 in reply to 1
Working as expected, I think. I wonder what would happen if <code>bar</code> was a TABLE instead of a VIEW, but still had the same TRIGGER, which still didn't have a RETURNING clause. Or suppose, inside of your TRIGGER, there were two INSERT commands. What would you expect to happen ?
(3) By happy-barney on 2021-07-25 14:23:46 in reply to 2 [link]
expected behaviour? take all inserted rows, apply view in read mode on them, and return expression referring result of this read. Multiple inserts in fact were primary reason why I tried this. Than magic with last_insert_rowid will be not sufficient enough.