SQLite触发器异常：插入后更新总曝光时间仅生效第一行求解决方案

问题背景 #

现有两张表：

• Astro_temp1：存储数百个图像文件的头部信息
• Astro_Target_Imaged_Date_Exposure：基于曝光时长的信息汇总表

汇总表已从临时表正确创建，包含12行数据（对应不同日期和曝光时长）。需创建插入后触发器，在向汇总表插入行后，更新该行的总曝光时间（即临时表中所有匹配该行条件的记录的曝光时间总和）。

当前触发器执行异常：仅第一行插入时正确计算sum(at1_exposure)，后续所有行均复用该计算值。

当前触发器中的UPDATE语句 #

UPDATE astro_target_imaged_date_exposure
SET 
ATIDE_TotalExposure= (select sum(at1_exposure)
FROM astro_target_imaged_date_exposure,
astro_temp1
where astro_temp1.AT1_Object = astro_target_imaged_date_exposure.ATI_TargetName
and astro_temp1.AT1_TELESCOP = astro_target_imaged_date_exposure.ATI_Telescope
and astro_temp1.AT1_FOCALLEN = astro_target_imaged_date_exposure.ATI_FocalLength
and astro_temp1.AT1_SessionDate = astro_target_imaged_date_exposure.ATID_CCYYMMDD
and astro_temp1.AT1_EXPOSURE = astro_target_imaged_date_exposure.ATIDE_Exposure_Amount
group by astro_target_imaged_date_exposure.ATI_TargetName,
    astro_target_imaged_date_exposure.ATI_Telescope, 
    astro_target_imaged_date_exposure.ATI_FocalLength,
    astro_target_imaged_date_exposure.ATID_CCYYMMDD,
    astro_target_imaged_date_exposure.ATIDE_Exposure_Amount)

表定义 #

Astro_Temp1表 #

CREATE TABLE Astro_Temp1 (
    AT1_Object        TEXT,
    AT1_TELESCOP      TEXT,
    AT1_FOCALLEN      TEXT,
    AT1_ICAMERA       TEXT,
    AT1_GAIN          TEXT,
    AT1_GSCOPE        TEXT,
    AT1_GCAMERA       TEXT,
    AT1_MOUNT         TEXT,
    AT1_ROTNAME       TEXT,
    AT1_ROTATOR       TEXT,
    AT1_FOCNAME       TEXT,
    AT1_SessionDate   TEXT,
    AT1_FWHEEL        TEXT,
    AT1_FILTER        TEXT           DEFAULT RGB,
    AT1_EXPOSURE      NUMERIC (6, 2),
    AT1_DateLoc       TEXT,
    AT1_DateUTC       TEXT,
    AT1_UTC_OffSet    INTEGER,
    AEC_Constant_Name TEXT
);

Astro_Target_Imaged_Date_Exposure表 #

CREATE TABLE Astro_Target_Imaged_Date_Exposure (
    ATI_TargetName             TEXT           NOT NULL,
    ATI_Telescope              TEXT           NOT NULL,
    ATI_FocalLength            TEXT           NOT NULL,
    ATID_CCYYMMDD              TEXT           NOT NULL,
    ATIDE_Exposure_Amount      NUMERIC (6, 2) DEFAULT (0),
    ATIDE_Filter               TEXT,
    ATDE_TotalApprovedExposure NUMERIC (6, 2) DEFAULT (0),
    ATIDE_TotalExposure        INTEGER        DEFAULT (0),
    ATIDE_Exposure_High_HFR    NUMERIC        DEFAULT (0),
    ATIDE_Exposure_Low_HFR     NUMERIC        DEFAULT (0),
    ATIDE_Approved_High_HFR    NUMERIC        DEFAULT (0),
    ATIDE_Approved_Low_HFR     NUMERIC        DEFAULT (0),
    PRIMARY KEY (
        ATI_TargetName,
        ATI_Telescope,
        ATI_FocalLength,
        ATID_CCYYMMDD,
        ATIDE_Exposure_Amount,
        ATIDE_Filter
    )
);

解决建议 #

问题根源 #

当前UPDATE语句未限定仅更新刚插入的行，而是对全表执行更新；同时子查询关联了汇总表本身，导致产生笛卡尔积，当子查询返回多行结果时，数据库会默认取第一行的值复用给所有更新行。

修正方案 #

利用数据库提供的插入虚拟表（如SQL Server的INSERTED、SQLite的new），精准定位刚插入的记录，单独计算其对应的总曝光时间。

适用于SQL Server的修正语句

UPDATE Astro_Target_Imaged_Date_Exposure
SET ATIDE_TotalExposure = (
    SELECT SUM(at1_exposure)
    FROM Astro_temp1
    WHERE 
        AT1_Object = INSERTED.ATI_TargetName
        AND AT1_TELESCOP = INSERTED.ATI_Telescope
        AND AT1_FOCALLEN = INSERTED.ATI_FocalLength
        AND AT1_SessionDate = INSERTED.ATID_CCYYMMDD
        AND AT1_EXPOSURE = INSERTED.ATIDE_Exposure_Amount
)
WHERE 
    ATI_TargetName = INSERTED.ATI_TargetName
    AND ATI_Telescope = INSERTED.ATI_Telescope
    AND ATI_FocalLength = INSERTED.ATI_FocalLength
    AND ATID_CCYYMMDD = INSERTED.ATID_CCYYMMDD
    AND ATIDE_Exposure_Amount = INSERTED.ATIDE_Exposure_Amount
    AND ATIDE_Filter = INSERTED.ATIDE_Filter;

适用于SQLite的修正语句

UPDATE Astro_Target_Imaged_Date_Exposure
SET ATIDE_TotalExposure = (
    SELECT SUM(at1_exposure)
    FROM Astro_temp1
    WHERE 
        AT1_Object = new.ATI_TargetName
        AND AT1_TELESCOP = new.ATI_Telescope
        AND AT1_FOCALLEN = new.ATI_FocalLength
        AND AT1_SessionDate = new.ATID_CCYYMMDD
        AND AT1_EXPOSURE = new.ATIDE_Exposure_Amount
)
WHERE 
    ATI_TargetName = new.ATI_TargetName
    AND ATI_Telescope = new.ATI_Telescope
    AND ATI_FocalLength = new.ATI_FocalLength
    AND ATID_CCYYMMDD = new.ATID_CCYYMMDD
    AND ATIDE_Exposure_Amount = new.ATIDE_Exposure_Amount
    AND ATIDE_Filter = new.ATIDE_Filter;

关键说明 #

• 通过INSERTED/new虚拟表限定仅处理刚插入的行，避免无意义的全表更新
• 子查询直接关联临时表与插入行的条件，消除笛卡尔积导致的计算错误
• 匹配汇总表主键的所有字段，确保精准定位到目标记录

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