Re: Behavior change around triggers between 3.19 and 3.30

On Thu, Jan 30, 2020 at 3:38 PM Graham Holden <[email protected]> wrote:
> Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne <[email protected]> wrote:
> > The strange thing though, is that I can't repro on a small example.
> > Despite using not_there in the trigger, and doing DML and ALTER TABLE,
> > still doesn't fail the same way as in production. What could be the cause? --DD
>
> I suspect it may only kick-in if you use ALTER TABLE to rename either
> a table or column. As I understand it, the old behaviour was to JUST
> rename the table/column; the new behaviour also scan triggers etc. and
> renames any references to the table/column as well. Since a new column
> cannot (shouldn't?) be referenced by a trigger, there's no real need
> to check them.

Good point. Confirmed below it seems. Thanks Graham. --DD

3.19.3 happily renamed the table, and the trigger is still invalid.
3.30.1 error'd out with: Error: error in trigger t2_on_update_ko: no
such column: OLD.not_there

------------ Console#1 on 3.19.3 ------------
sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on t2
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on t2 when OLD.not_there != NEW.not_there begin update t2bis
set v=NEW.v where id=OLD.id; END

sqlite> alter table t2 rename to t2a;

sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a"
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on "t2a" when OLD.not_there != NEW.not_there begin update
t2bis set v=NEW.v where id=OLD.id; END
sqlite>

------------ Console#2 on 3.30.1 ------------
sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a"
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on "t2a" when OLD.not_there != NEW.not_there begin update
t2bis set v=NEW.v where id=OLD.id; END

sqlite> alter table t2a rename to t2b;
Error: error in trigger t2_on_update_ko: no such column: OLD.not_there

sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a"
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on "t2a" when OLD.not_there != NEW.not_there begin update
t2bis set v=NEW.v where id=OLD.id; END
sqlite>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users