Triggers for generated columns?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Triggers for generated columns?

Stephen F. Booth
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>

Thanks,
Stephen
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Triggers for generated columns?

Keith Medcalf

On Monday, 2 December, 2019 17:10, Stephen F. Booth <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Triggers for generated columns?

Stephen F. Booth
Hi Keith,

Thanks for the reply.

On Mon, Dec 2, 2019 at 6:31 PM Keith Medcalf <[hidden email]> wrote:

> 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.


You're right- this was a copy/paste error in my attempt to produce a brief
example.


> 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).


This is very helpful. I had assumed the trigger would fire for any update
of the column, computed or otherwise.

Thanks again.

Stephen
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users