On Monday, 2 December, 2019 17:10, Stephen F. Booth <m...@sbooth.org> wrote:
>I have been trying out generated column support in the 3.31.0 prerelease.
>Thank you for adding such a useful feature!
>When I create a trigger for an update of a generated column the trigger
>is successfully created but it never fires. I could not find any mention in
>the draft documentation of generated columns in the context of triggers,
>so I'm not sure what should be expected.
>Here is an example of what I tried:
>sqlite> .version
>SQLite 3.31.0 2019-11-19 21:22:16
>a48f6e17599d7ec794cfcd60858d6a69cf05caaae9321048b111fedd86a3b5b2
>zlib version 1.2.11
>clang-11.0.0
>sqlite> create table t0(a,b,c as (a+b) stored);
>sqlite> create table t1(k,v);
>sqlite> create trigger trig1 after update of c on t0 begin insert into
>t1(k,v) values ('c',new.c); end;
>sqlite> insert into t0(a,b) values (1,2),(3,4);
>sqlite> select * from t0;
>1|2|3
>3|4|7
>sqlite> select * from t1;
>sqlite>
Well, of course the trigger did not fire. You created an AFTER UPDATE trigger,
but only did an insert. So of course one would not expect the trigger to fire.
However, you are correct that even if you did do an update the trigger would
not fire, because YOU did not update "c" because you cannot. You can, however,
update "a" or "b" and fire an appropriate trigger:
SQLite version 3.31.0 2019-12-01 22:39:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t0(a,b,c as (a+b) stored);
sqlite> create table t1(k,v);
sqlite> create trigger trig1 after update of c on t0 begin insert into t1(k,v)
values ('c',new.c); end;
sqlite> insert into t0(a,b) values (1,2),(3,4);
sqlite> select * from t0;
1|2|3
3|4|7
sqlite> select * from t1;
sqlite> update t0 set a=4 where a=1;
sqlite> select * from t0;
4|2|6
3|4|7
sqlite> select * from t1;
sqlite> create trigger trig2 after update on t0 when new.c != old.c begin
insert into t1(k,v) values ('c',new.c); end;
sqlite> update t0 set a=7 where a=3;
sqlite> select * from t0;
4|2|6
7|4|11
sqlite> select * from t1;
c|11
You just have to use the correct trigger in the correct form. An UPDATE
trigger only fires for UPDATE, not for INSERT. INSERT triggers fire for
INSERT. Also, the "of <columns> on <table>" refers to columns that YOU name in
the INSERT/UPDATE statement and does not include computed columns, since you do
not update them (you will get an error message telling you that you cannot be
doing that if you try to do that).
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
