Re: Behavior change around triggers between 3.19 and 3.30

On Thu, Jan 30, 2020 at 1:09 PM Richard Hipp <[email protected]> wrote:
> On 1/30/20, Dominique Devienne <[email protected]> wrote:
> > My first question would be to ask whether there's a pragma or
> > compile-time option to get back to the old behavior?
>
> Did you try "PRAGMA legacy_alter_table=ON;"?

BINGO!!! Thanks a bunch Richard.

> > Second, any idea when this was introduced?
> People have been requesting enhanced ALTER TABLE support.  In order to
> provide that, we had to change ALTER TABLE to do a full parse of the
> entire schema, so that it can find all of the bits and pieces that
> need altering.  This means that ALTER TABLE now also finds latent
> syntax errors in the schema.

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

------------ Console #1 ------------
c:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> create table t1(v);
sqlite> create table t2(id INTEGER PRIMARY KEY, v);
sqlite> create table t2bis(id INTEGER PRIMARY KEY, v);
sqlite> create trigger t2_on_insert_ok after insert on t2 begin insert
into t2bis(id,v) values(NEW.id, NEW.v); END;
sqlite> 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> .exit

c:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> select name, type from sqlite_master;
t1|table
t2|table
t2bis|table
t2_on_insert_ok|trigger
t2_on_update_ko|trigger
sqlite>
sqlite> insert into t1(v) values (1);
sqlite> insert into t2(v) values (1, 'one');
Error: 2 values for 1 columns
sqlite> insert into t2(id, v) values (1, 'one');
sqlite> update t2 set v = 'uno' where id = 1;
sqlite> alter table t1 add column v2;
sqlite> alter table t2 add column v2;
sqlite> pragma legacy_alter_table;
sqlite>

------------ Console #2 ------------
C:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> select name, type from sqlite_master;
t1|table
t2|table
t2bis|table
t2_on_insert_ok|trigger
t2_on_update_ko|trigger
sqlite> insert into t1(v) values (2);
sqlite> insert into t2(id, v) values (2, 'two');
sqlite> update t2 set v = 'dos' where id = 2;
sqlite> alter table t1 add column v3;
sqlite> alter table t2 add column v3;
sqlite> pragma legacy_alter_table;
0
sqlite>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users