Has anyone else run into this issue? Is there a workaround? I have the following tables and trigger....
- Data table CREATE TABLE people ( id INTEGER PRIMARY KEY, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, name TEXT, age INTEGER ); -- Change log table CREATE TABLE change_log ( id INTEGER PRIMARY KEY, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, action TEXT, table_name TEXT, obj_id INTEGER, changes TEXT ); -- Insert Trigger CREATE TRIGGER people_track_insert AFTER INSERT ON people BEGIN INSERT INTO change_log (action, table_name, obj_id, changes) SELECT 'INSERT', 'people', NEW.id, changes FROM (SELECT json_group_object(col, json_array(oldval, newval)) AS changes FROM (SELECT json_extract(value, '$[0]') as col, json_extract(value, '$[1]') as oldval, json_extract(value, '$[2]') as newval FROM json_each( json_array( json_array('id', null, NEW.id), json_array('created', null, NEW.created), json_array('name', null, NEW.name), json_array('age', null, NEW.age) ) ) WHERE oldval IS NOT newval ) ); END; If I then do the following, the trigger works correctly, updating the change_log table, etc $> sqlite3 test.db sqlite> INSERT INTO people (name, age) VALUES ('Alice', 30), ('Bob', 42); sqlite> UPDATE people SET age = age + 2; sqlite> UPDATE people SET name = 'Eva' WHERE name='Alice'; sqlite> DELETE FROM people WHERE name = 'Bob'; sqlite> SELECT * FROM change_log; However, the above queries for the same db, when 'attached', fails.: $> sqlite3 sqlite> attach './test.db' as test; ... In the attached case any inserts into the table cause the trigger to fail with something like the following error: *Error: near line 1694: no such table: napp.json_each* Somehow the virtual table json_each in the db trigger is not available for an 'attached' db. It does work correctly for the main db. It also works for an attached db as a normal query. I am running the latest amalgamation code with the json1 extension enabled sqlite-amalgamation-3250200.zip (2.17 MiB) C source code as an amalgamation, version 3.25.2. (sha1: c9ff08b91a0faacabe2acb240e5dba3cf81071f3) Compiled with: gcc -Os -I. -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_JSON1 -DHAVE_USLEEP -DHAVE_READLINE shell.c sqlite3.c -ldl -lreadline -lncurses -Os -o sqlite3 /Lindsay _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users