insert into view returning id returns null
(1) By happy-barney on 2021-07-24 14:35:37 [link] [source]
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 [source]
Working as expected, I think.
I wonder what would happen if bar
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] [source]
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.