我try 在SQLite中创建一个触发器(在INSERT之前或之后),它接受以下新行:
** events table **
id timeslots
1 [{start: 2023, end: 2024 }, { start: 2025, end: 2025 }, ...]
并将其保存到另一个表中:
** timeslots table **
id end start
1 2023 2024
1 2024 2025
1 ... ...
此时,我不起作用的触发器是:
CREATE TRIGGER IF NOT EXISTS insert_timeslots
AFTER INSERT ON events
BEGIN
INSERT INTO timeslots (id, start, end)
SELECT
json_extract(t.value, '$.id'),
json_extract(t.value, '$.start'),
json_extract(t.value, '$.end'),
FROM json_each(NEW.timeslots) AS t;
END;`
I've start a DBFiddle to help debugging.
Any clue? Thanks