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

Question

我正在尝试在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语句。一切都在触发器之外起作用，当我查阅 TRIGGER上的SQLite文档时，我注意到触发器内仅允许更新，插入，删除和选择语句-显然省略了WITH子句。

如何获得相同的效果，以便可以在查询的多个位置重用相同的时间戳值？

Answer 1

我将其重写为：

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;

DBFiddle Demo

编辑：

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;

DBFiddle Demo2



编辑：

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