如何创建嵌套表并仅向内层表插数，修改员工薪资时插入历史薪资？

这是个很实用的业务场景，我们可以通过Oracle数据库触发器来自动完成旧薪资的记录，不用每次手动操作嵌套表。下面是具体的实现方案：

核心思路 #

当employees表的salary字段被更新时，触发一个行级触发器，将更新前的薪资值追加到对应的salary_history嵌套表中。需要注意的是，如果嵌套表还未初始化（为空），要先创建一个空的嵌套表实例，否则直接添加元素会报错。

触发器实现代码 #

CREATE OR REPLACE TRIGGER trg_employees_salary_history
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    -- 初始化空的嵌套表（如果之前未赋值）
    IF :OLD.salary_history IS NULL THEN
        :NEW.salary_history := salary_list();
    END IF;
    -- 扩展嵌套表容量，添加旧薪资值
    :NEW.salary_history.EXTEND;
    :NEW.salary_history(:NEW.salary_history.LAST) := :OLD.salary;
END;
/

代码解释 #

• BEFORE UPDATE OF salary：限定只有当salary字段被修改时才触发触发器，避免不必要的执行
• FOR EACH ROW：行级触发器，每一条被更新的记录都会执行一次逻辑
• :OLD.salary：代表更新前的薪资值，我们需要把它存入历史表
• :NEW.salary_history.EXTEND：扩展嵌套表的容量，为新增元素预留空间
• :NEW.salary_history(:NEW.salary_history.LAST)：指向嵌套表的最后一个位置，将旧薪资赋值到这里

测试验证 #

比如我们更新ID为1的员工薪资：

UPDATE employees SET salary = 8500 WHERE id = 1;

之后查询该员工的信息，就能看到历史薪资已经被记录：

SELECT id, firstname, lastname, salary, salary_history 
FROM employees 
WHERE id = 1;

如果多次更新薪资，每次的旧值都会被依次追加到salary_history中，完美实现过往薪资的存储需求。

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