SQLite触发器使用WITH子句报错及子查询复用优化问询

我在开发嵌入式设备的生产线停机监控项目，目标是通过用户配置的通用规则实现停机自动确认。编写AFTER UPDATE触发器时，尽管文档表明支持WITH子句，但在UPDATE语句附近出现语法错误。

触发器代码 #

CREATE TRIGGER autoAcknowledge
    AFTER UPDATE OF dtEnd ON ackGroups
    FOR EACH ROW
    WHEN old.dtEnd IS NULL AND new.dtEnd IS NOT NULL
    BEGIN
        WITH sub1(id, stationId, groupDur) AS (
            SELECT MIN(d.id), d.station,
                strftime('%s', ag.dtEnd) - strftime('%s', ag.dtStart) 
                FROM ackGroups AS ag
                LEFT JOIN downtimes AS d on d.acknowledge = ag.id
                WHERE ag.id = old.id
                GROUP BY ag.id ),
            sub2( originId, groupDur, reasonId, above, ruleDur) AS (
                SELECT sub1.stationId, sub1.groupDur, aar.reasonId, aar.above, aar.duration
                FROM sub1
                LEFT JOIN autoAckStations AS aas ON aas.stationId = sub1.stationId
                LEFT JOIN autoAckRules AS aar ON aas.autoAckRuleId = aar.id
                ORDER BY duration DESC )
        UPDATE ackGroups SET (reason, dtAck, origin)=(
            SELECT reasonId, datetime('now'), originId
            FROM sub2 as s
            WHERE ( s.ruleDur < s.groupDur AND above = 1 ) OR (s.ruleDur > s.groupDur AND above = 0)
            LIMIT 1
        )
        WHERE id = old.id;
    END

表结构背景 #

1. downtimes表：每条生产线包含多个station，每个station可触发停机，停机时间可能重叠。

CREATE TABLE "downtimes" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
    "station" integer NOT NULL, 
    "acknowledge" integer, 
    "dtStart" datetime NOT NULL, 
    "dtEnd" datetime, 
    "dtLastModified" datetime)

2. ackGroups表：重叠停机通过downtimes表的AFTER INSERT触发器分组为确认组。

CREATE TABLE "ackGroups" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
    "reason" integer, 
    "dtAck" datetime, 
    "dtStart" datetime NOT NULL, 
    "dtEnd" datetime, 
    "line" integer NOT NULL, 
    "origin" integer)

3. autoAckRules表：存储用户配置的自动确认规则，用户可设置规则适用的时长范围及对应reasonId。

CREATE TABLE "autoAckRules" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
    "description" text NOT NULL, 
    "reasonId" integer NOT NULL, 
    "above" bool NOT NULL, 
    "duration" real NOT NULL)

4. autoAckStations表：管理规则与station的M:N关系。

CREATE TABLE autoAckStations (
        autoAckRuleId INTEGER NOT NULL,
        stationId INTEGER NOT NULL,
        PRIMARY KEY ( autoAckRuleId, stationId )
    )

业务逻辑 #

当最后一条停机记录结束时，ackGroups的dtEnd被设为datetime('now')，触发上述触发器匹配自动确认规则。

疑问 #

如果改用嵌套子查询（SELECT .. FROM( SELECT .. FROM(SELECT .. FROM ))）替代WITH子句，有没有办法避免重复编写和计算？或者我在触发器代码中存在某些疏忽导致语法错误？

内容的提问来源于stack exchange，提问作者John Bart