Oracle SQL新增locations表字段及departments联动更新触发器开发需求

1. 为locations表新增department_amount字段 #

设置默认值为0避免空值问题：

ALTER TABLE locations 
ADD department_amount NUMBER DEFAULT 0 NOT NULL;

2. 为字段添加注释 #

COMMENT ON COLUMN locations.department_amount 
IS 'Contains the amount of departments in the location';

3. 创建联动更新触发器 #

触发器会在departments表发生插入、删除操作时，自动同步对应location的部门统计数量：

CREATE OR REPLACE TRIGGER trg_update_dept_amount
AFTER INSERT OR DELETE ON departments
FOR EACH ROW
BEGIN
  -- 插入新部门时，对应地点的部门数+1
  IF INSERTING THEN
    UPDATE locations
    SET department_amount = department_amount + 1
    WHERE location_id = :NEW.location_id;
  END IF;

  -- 删除部门时，对应地点的部门数-1
  IF DELETING THEN
    UPDATE locations
    SET department_amount = department_amount - 1
    WHERE location_id = :OLD.location_id;
  END IF;
END;
/

补充说明：

1. 如果需要支持修改部门所属地点时也自动更新统计数，可将触发器触发条件调整为 AFTER INSERT OR DELETE OR UPDATE OF location_id ON departments，并在触发器逻辑中新增UPDATING分支，分别修改旧地点和新地点的统计值即可。
2. 若当前departments表已有历史数据，新增字段后需要执行初始化统计保证数据一致：

UPDATE locations l
SET department_amount = NVL((
  SELECT COUNT(*) 
  FROM departments d 
  WHERE d.location_id = l.location_id
), 0);
COMMIT;

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