PL/SQL触发器对employees表新插入数据不生效问题排查

• 触发器WHEN条件不兼容INSERT场景：INSERT操作时不存在旧行数据，所有:old前缀的字段值都是NULL，NULL和任意值做不等比较的结果为UNKNOWN，不满足触发条件，因此所有INSERT操作都会直接跳过触发器。
• 触发器传参逻辑错误：就算INSERT场景能触发，你给存储过程传的第一个参数是:old.job_id，INSERT时该值为NULL，存储过程查询jobs表匹配不到任何数据，不会执行校验逻辑；哪怕是UPDATE场景，如果用户修改了员工的岗位编号，你用旧岗位的薪资范围校验新薪资，逻辑本身也是错误的。
• 部分插入操作未指定薪资值：你测试用的INSERT语句没有显式赋值salary字段，如果employees表的salary字段没有设置默认值，:new.salary为NULL，就算触发校验也无法完成合法判断。

1. 修正触发器逻辑 #

调整WHEN触发条件和传参，兼容插入、更新两种场景：

CREATE OR REPLACE TRIGGER check_salary_trg
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (
  -- 插入场景：只要薪资和岗位编号非空就校验
  INSERTING 
  OR 
  -- 更新场景：薪资或岗位编号发生变动时校验
  (UPDATING AND (new.salary != old.salary OR new.job_id != old.job_id))
)
BEGIN
    -- 始终用新的岗位编号和新的薪资做校验
    check_salary(:new.job_id, :new.salary);
END check_salary_trg;

2. 补充配套校验 #

• 给employees表的salary、job_id字段添加非空约束，避免插入空值跳过校验
• 可以优化check_salary存储过程，增加入参非空判断、岗位不存在时的报错逻辑，避免无提示的校验失效：

CREATE OR REPLACE PROCEDURE check_salary (pjobid employees.job_id%type, psal employees.salary%type)
IS
    v_min_sal jobs.min_salary%type;
    v_max_sal jobs.max_salary%type;
BEGIN
    -- 入参非空校验
    IF pjobid IS NULL OR psal IS NULL THEN
        RAISE_APPLICATION_ERROR(-20002, '岗位编号和薪资不能为空');
    END IF;

    SELECT min_salary, max_salary INTO v_min_sal, v_max_sal
    FROM jobs
    WHERE job_id = pjobid;

    IF psal < v_min_sal OR psal > v_max_sal THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid salary ' || psal || '. Salaries for job ' || pjobid || ' must be between ' || v_min_sal || ' and ' || v_max_sal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Salary is okay!');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20004, '岗位编号'||pjobid||'不存在');
END check_salary;

• 如果你需要支持插入时不指定薪资的场景，可以给salary字段设置一个明显非法的默认值（比如0），插入时会自动触发校验提醒你填写合法薪资。

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