SQLite中delete触发器执行后求和为null时如何将值更新为0？

问题根源 #

你的触发器逻辑无效的核心原因是SQL的NULL值比较规则：SQL中= NULL的判断结果永远为假，你写的CASE SUM(A.UNITS_AVAILABLE) WHEN NULL THEN 0分支永远不会被触发，因此当对应编码的sub表记录全部删除后，SUM()返回的NULL会直接赋值给main表字段，导致结果异常。

解决方案 #

直接使用SQLite内置的IFNULL()函数处理聚合结果即可，写法更简洁，也能完美覆盖空值场景，修正后的触发器代码如下：

CREATE TRIGGER UpdateAmountAvailable_DELETE
AFTER DELETE ON Sub
BEGIN
    UPDATE Main
    SET TOTAL_UNITS_AVAILABLE = IFNULL(
        (SELECT SUM(A.UNITS_AVAILABLE) FROM Sub AS A WHERE A.INGREDIENT_CODE = old.INGREDIENT_CODE),
        0
    ) 
    WHERE CODE = old.INGREDIENT_CODE;
END

补充说明 #

如果你坚持要使用CASE语句实现，需要把空值判断逻辑调整为IS NULL格式，示例如下：

SELECT
    CASE 
        WHEN SUM(A.UNITS_AVAILABLE) IS NULL THEN 0
        ELSE SUM(A.UNITS_AVAILABLE)
    END
FROM Sub AS A
WHERE A.INGREDIENT_CODE = old.INGREDIENT_CODE

另外你原代码的触发器命名和触发时机不匹配，建议将DELETE触发器的名称修改为带DELETE后缀的命名，避免和UPDATE触发器重名冲突。

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