This had me scratching my head for a while. Normally, when you rename a table with triggers on it using ALTER TABLE old_name RENAME TO new_name, it will fix the table association in sqlite_master to indicate the relationship with the new table. However, it turns out, if the table name is specified with different case in the create table statement and the create trigger statement, the triggers are not actually fixed.
I ran the following script in SQLite 3.7.10 on Solaris 9 in the shell program, using a brand-new UTF-8 database: .echo 1 .width 36 create table Old_table (c1 integer); create trigger old_table_insert_trigger after insert on old_table for each row begin select 1; end; select name,tbl_name from sqlite_master; alter table old_table rename to new_table; select name,tbl_name from sqlite_master; And the second select showed old_table_insert_trigger as belonging to old_table. Given there wasn't an old table at that point, this is a bit of a problem. Also, once in this state, the .tables command fails. Also, drop trigger doesn't work on a trigger in this state. However, if you change the create trigger statement slightly to: create trigger old_table_insert_trigger after insert on Old_table for each row begin select 1; end; And then everything works fine. Now, I can see what's going wrong in sqlite3AlterRenameTable -- the where clauses in the SQL used to detect triggers to fix are case sensitive. But what strikes me as the most obvious fix -- using lower() or upper() to make the comparison case-insensitive -- might be problematic on non-ASCII table names if ICU isn't being used. I'm not sure how much of an issue that is. Best regards, Peter _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
