Hello Keith, On Thu, Oct 4, 2018 at 1:16 AM Keith Medcalf <kmedc...@dessus.com> wrote:
> > Unrelated, but are you sure that you want the albums "id int primary key" > and did not happen to misspell "integer" so that the declaration should be > "id integer primary key". In the former case, id is an integer that just > happens to be unique (ie, "id int primary key" is the same as "id integer > unique") and not an explicitly named alias for the rowid (which requires > correct spelling of the phrase "integer primary key"). > I though "int" was the same as "integer" ( https://www.sqlite.org/datatype3.html). Thanks. > Second unrelated, do you not want an affinity for the album_id column in > rates? Should not you have declared it as "album_id integer references > albums(id) on delete cascade"? > Correct. Third unrelated, do not forget to create an index on the foreign key (as in > "CREATE INDEX idxRates_album_id on rates (album_id)" for example). > Sure. Fourth unrelated, do you want the title and comment_text to be case > sensitive or should they have COLLATE NOCASE? > No. > As to the issue with the updated table rename, you can either use a > version of sqlite3 that does not have the alter table rename updates, or > for version 3.25.2 use the pragma "PRAGMA legacy_alter_table=ON" to avoid > using the new "change the table names in triggers etc" features added in > 3.25.0 so that you can continue to use the old method of just "substituting > tables". > Indeed, use the pragma, thanks! https://www.sqlite.org/pragma.html#toc using pragma, the following script output 5, which is expected result. drop table if exists albums; drop table if exists rates; -- create table albums (id integer primary key, title text, score int); create table rates (album_id integer references albums(id) on delete cascade, score int); create trigger test after insert on rates begin update albums set score=new.score where id=new.album_id; end; -- insert into albums (id, title) values (1, 'Cheap Thrills'); -- begin; pragma legacy_alter_table=ON; drop table if exists new_albums; create table new_albums (id int primary key, title text not null, score int); insert into new_albums (id, title, score) select id, title, score from albums; drop table albums; alter table new_albums rename to albums; pragma legacy_alter_table=OFF; end; -- insert into rates values (1, 5); select score from albums; When I comment the pragma, I have Error: near line 16: error in trigger test: no such table: main.albums Error: near line 20: no such table: main.albums Error: near line 21: no such table: albums It is a resolution for the problem I faced : add a constraint to colum of a table referenced in a trigger. Do you believe ? a) https://www.sqlite.org/lang_altertable.html#otheralter could be updated. b) rename A to B should not fail in the face of a trigger referencing B. ? , Thierry _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users