如何为已填充的Locations表添加统计对应部门数的nb_departments列？

一、ALTER TABLE ADD COLUMN DEFAULT VALUE 无法实现需求 #

Oracle 中，ALTER TABLE 添加列时的 DEFAULT 子句仅支持常量或确定性内置函数（比如 SYSDATE），无法引用其他表的动态聚合结果。要设置的 nb_departments 初始值是基于 departments 表中对应地点的部门数量，属于依赖其他表的动态计算值，因此直接用 DEFAULT 无法完成这个需求。

二、触发器可以实现，但需分步骤操作 #

要实现初始值填充+后续数据自动维护，需要分三步进行：

1. 添加字段
   先给 locations 表新增字段，可先设默认值为0（后续会覆盖）：

ALTER TABLE locations ADD nb_departments NUMBER(4) DEFAULT 0;

2. 初始化现有数据
   执行 UPDATE 语句，将现有每个地点对应的部门数量填充到新字段中：

UPDATE locations l
SET nb_departments = (
    SELECT COUNT(*)
    FROM departments d
    WHERE d.location_id = l.location_id
);
COMMIT;

3. 创建触发器维护数据一致性
   为了保证后续 departments 表数据变化（插入、删除、部门转移地点）时，locations 表的 nb_departments 能自动更新，需要创建行级触发器：

CREATE OR REPLACE TRIGGER trg_departments_loc_count
AFTER INSERT OR DELETE OR UPDATE OF location_id ON departments
FOR EACH ROW
BEGIN
    -- 处理新地点的计数增加：插入新部门，或部门转移到新地点
    IF INSERTING OR (UPDATING AND :NEW.location_id != :OLD.location_id) THEN
        UPDATE locations
        SET nb_departments = nb_departments + 1
        WHERE location_id = :NEW.location_id;
    END IF;

    -- 处理旧地点的计数减少：删除部门，或部门从旧地点转移走
    IF DELETING OR (UPDATING AND :NEW.location_id != :OLD.location_id) THEN
        UPDATE locations
        SET nb_departments = nb_departments - 1
        WHERE location_id = :OLD.location_id;
    END IF;
END;
/

这样既完成了现有数据的初始化，也能自动维护后续数据的准确性。

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