如何在 SQLite 触发器中使用 WITH 子句

我正在尝试在 SQLite 数据库中创建某些内容的日志。我正在使用触发器执行此操作，但我需要插入多个日志记录，并且它们都需要具有相同的时间戳。为了做到这一点，我正在尝试使用WITH子句来获取当前时间戳，然后我可以在多个地方使用它。

我的声明看起来像这样：

CREATE TRIGGER test7_INSERT AFTER INSERT ON test7 
BEGIN
    WITH t(t) AS (
        SELECT CAST((julianday('now') - 2440587.5)*86400000 
                    AS INTEGER)
    )
    INSERT INTO ChangeLog (t, rowid, field, value)
    VALUES 
    (t.t, 1, 'field1', new.field1),
    (t.t, 1, 'field2', new.field2),
    (t.t, 1, 'field3', new.field3);
END;

但是当我尝试运行此语句时，我在“INSERT”附近收到语法错误。我已重命名其他 INSERT，以将其与触发器本身内的 INSERT 语句隔离。一切都在触发器之外工作，当我查阅 SQLite 文档时TRIGGER https://sqlite.org/lang_createtrigger.html，我注意到触发器内只允许更新、插入、删除和选择语句——显然省略了WITH子句。

如何才能达到相同的效果，以便可以在查询中的多个位置重用相同的时间戳值？

我会将其重写为：

INSERT INTO ChangeLog (t, rowid, field, value)
SELECT ":1", ":2", ":3",CAST((julianday('now') - 2440587.5)*86400000  AS INTEGER)
FROM (VALUES
(t.t, 1, 'field1', new.field1),
(t.t, 1, 'field2', new.field1),
(t.t, 1, 'field3', new.field1) ) sub;

EDIT:

WITH cte(c1,c2,c3,c4) AS (
  VALUES  (t.t, 1, 'field1', new.field1),
          (t.t, 1, 'field2', new.field1),
          (t.t, 1, 'field3', new.field1) 
)
INSERT INTO ChangeLog (t, rowid, field, value)
SELECT c1,c2,c3, CAST((julianday('now') - 2440587.5)*86400000  AS INTEGER)
FROM cte;

EDIT:

INSERT INTO ChangeLog (t, rowid, field, value)
SELECT c1,c2,c3, CAST((julianday('now') - 2440587.5)*86400000  AS INTEGER)
FROM (
    SELECT t.t AS c1, 1 AS c2, 'field1' AS c3, new.field1 AS c4 UNION ALL
    SELECT t.t AS c1, 1 AS c2, 'field2' AS c3, new.field1 AS c4 UNION ALL
    SELECT t.t AS c1, 1 AS c2, 'field3' AS c3, new.field1 AS c4;
) sub