我正在进行一个项目，用嵌入式设备监控生产线的停机时间。我想通过用户可以配置的通用规则自动确认这些停机时间。我想使用TRIGGER，但在UPDATE附近出现语法错误，尽管文档中说使用WITH语句应该没问题。

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

背景：首先我们有停工时间表。每条生产线由多个称为工位的部分组成。每个工位可以启动生产线停工时间，也可以与其他工位的停工时间重叠。

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

重叠的停机时间被分组到确认组，在停机时间使用TRIGGER AFTER INSERT正确设置确认ID或创建新组。

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)

autoAckRules表表示配置。用户决定是否应将规则应用于高于或低于某个值的持续时间，以及应使用哪个rasonId进行确认。

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)

autoAckStations表用于管理M：N关系。每个规则允许多个工作站启动ackGroup。

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

当最后一次停机结束时，ackGroups的dtEnd被设置为datetime('now')，并且触发器被激发以检查是否存在适合的autoAckRule。
如果我用SELECT .. FROM( SELECT .. FROM(SELECT .. FROM )))级联替换子选择，是否有一种好方法可以避免两次写入和求值？
或者我错过了什么愚蠢的事情？