On Thu, Jan 30, 2020 at 3:38 PM Graham Holden <sql...@aldurslair.com> wrote: > Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne > <ddevie...@gmail.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
