You can maintain a local conditional "index" table via temp trigger, as long as there's no other processes modify the permanent table.
Create table t(id integer primary key, x); Create temp table t1(id integer primary key); Create temp trigger trig_1 after insert on t begin Insert into t1 select new.id where new.x > 10000; end; ________________________________ 发件人: Smith, Randall<mailto:rsm...@qti.qualcomm.com> 发送时间: 2016/7/23 9:06 收件人: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> 主题: [sqlite] REFERENCES from TEMPORARY table to main database table? Hi. I'm creating a specialized index table to a persistent table that I need to speed up a one-time operation. This seems like a great application for a TEMPORARY table in SQLite, so the index will always be reliably cleaned up. However, I can't find a way to have REFERENCES to the main table appear in the temp table. I want, e.g. CREATE TEMPORARY TABLE MySpecializedIndex ( -- etc. Symbol INTEGER REFERENCES Symbols(rowid) ) For which I get an operational error "no such table: temp.Symbols". Using "REFERENCES main.Symbols" appears to be a syntax error. Is it impossible to have references from temp tables to main tables? If so, aren't TEMPORARY tables largely useless? Is there another idiom in SQLite for managing tables that are intended to have a short life or which should be reliably cleaned up when the DB closes? Thanks for any information and suggestions! Randall. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
