On 12/03/2018 10:37 AM, Philip Warner wrote:
Tables with complex triggers (possibly limited to "Insert...With....", though that is not clear), fail with "no such table".
Thanks for reporting this. The bug was that table and column names within WITH clauses in the schema were not being updated correctly. Now fixed here:
https://www.sqlite.org/src/info/f44bc7a8b3fac82a Dan.
The following produces the error in 3.26; a much simpler trigger does not produce the error. |Create Table LOG_ENTRY(|| || LOG_ENTRY_ID int primary key,|| || TIME_START Long,|| || TIME_FINISH Long|| || );|| || ||Create Table SEGMENTS(START Long, FINISH Long);|| || ||Create View SEGMENTS_REBUILD_PV(START, FINISH) as Select Null, Null;|| || ||Drop Trigger if Exists SEGMENTS_REBUILD_PV_TG;|| ||Create Trigger SEGMENTS_REBUILD_PV_TG || || Instead of Insert on SEGMENTS_REBUILD_PV|| ||-- Recreate the SEGMENTS entries in the specified range|| ||Begin|| || Delete from SEGMENTS Where || || START <= New.FINISH || || And FINISH >= New.START;|| || || Insert or Replace into SEGMENTS(START, FINISH) || || With GAP(START, FINISH) as || || (Select|| || -- Nearest break before New.START (or New.START,|| || -- if nothing before).|| || Max( Coalesce((Select Max(TIME_FINISH)|| || From LOG_ENTRY E|| || Where E.TIME_FINISH < New.START), New.START),|| || Coalesce((Select Max(TIME_START)|| || From LOG_ENTRY E|| || Where E.TIME_START < New.START), New.START)|| || ),|| || -- Nearest break after New.FINISH (or New.FINISH ,|| || -- if nothing after).|| || Min( Coalesce((Select Min(TIME_START)|| || From LOG_ENTRY E|| || Where E.TIME_START > New.FINISH), New.FINISH),|| || Coalesce((Select Min(TIME_FINISH)|| || From LOG_ENTRY E|| || Where E.TIME_START > New.FINISH), New.FINISH))|| || ),|| || LOGS as|| || ( Select * from LOG_ENTRY E, GAP|| || Where E.TIME_START <= GAP.FINISH|| || And E.TIME_FINISH >= GAP.START|| || )|| || Select Distinct B.START, B.FINISH From|| || GAP,|| || ( Select|| || BREAK as START, || || Lead(BREAK) Over (Order by BREAK) as FINISH|| || From || || ( || || Select Distinct TIME_START as BREAK from LOGS|| || UNION|| || Select Distinct TIME_FINISH as BREAK from LOGS|| || )|| || ) B || || Where B.FINISH is Not NULL || || and B.START < GAP.FINISH|| || and B.FINISH >= GAP.START|| || ;|| ||End;|| | |Alter table LOG_ENTRY Rename To ZZZ;| |Error: error in trigger SEGMENTS_REBUILD_PV_TG after rename: no such table: main.LOG_ENTRY| _______________________________________________ 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
