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
