Tables with complex triggers (possibly limited to "Insert...With....", though
that is not clear), fail with "no such table".
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
