如何编写PostgreSQL触发器校验grade列插入更新值并抛出异常

需求背景 #

需要为已存在的SQL表hr."position"的grade列添加触发器，实现写入校验：向该列插入新值、修改已有行的该列值时，若数值不在合法范围则抛出异常阻止写入。当前已编写的示例代码仅能在插入新行时触发，更新场景下校验不生效。

待解决问题 #

• 如何调整触发器配置，覆盖已有行grade列修改的场景，触发校验逻辑抛出对应提示？
• grade列的合法值通过外键关联存储在grade_salary表的grade列中，如何改写校验逻辑，无需硬编码2、3、5-7这类固定值，实现：插入/修改后的grade值不在grade_salary的合法值集合中时，抛出异常阻止非法写入。

现有示例代码 #

CREATE TRIGGER person
BEFORE INSERT ON hr."position"
FOR EACH ROW EXECUTE PROCEDURE person();

CREATE OR REPLACE FUNCTION person()
RETURNS TRIGGER
SET SCHEMA 'hr'
LANGUAGE plpgsql
AS $$
BEGIN
IF ((NEW.grade < 2) or (NEW.grade > 3 and NEW.grade < 5)
or (NEW.grade > 7)) THEN
    RAISE EXCEPTION 'Incorrect value';
END IF;
RETURN NEW;
END;
$$;

实现方案 #

触发器配置调整（覆盖更新场景） #

原有触发器仅绑定了BEFORE INSERT事件，无法捕获更新操作。直接新增一个绑定BEFORE UPDATE事件的触发器，复用同一个校验函数即可，不需要重复编写逻辑。推荐使用UPDATE OF grade语法，仅当grade字段被修改时才触发校验，减少无意义的性能开销：

-- 先删除原有命名不规范的触发器
DROP TRIGGER IF EXISTS person ON hr."position";

-- 插入场景校验触发器
CREATE TRIGGER trg_position_grade_validate_insert
BEFORE INSERT ON hr."position"
FOR EACH ROW EXECUTE FUNCTION hr.person();

-- 更新场景校验触发器，仅grade列变更时触发
CREATE TRIGGER trg_position_grade_validate_update
BEFORE UPDATE OF grade ON hr."position"
FOR EACH ROW EXECUTE FUNCTION hr.person();

提示：PostgreSQL 11+版本推荐使用标准语法EXECUTE FUNCTION替代旧版的EXECUTE PROCEDURE，两者执行效果完全一致。

动态校验逻辑改写（关联外键表取值） #

删除原函数中硬编码的数值范围判断，改为查询grade_salary表判断新写入的grade值是否存在于合法值集合中，修改后的函数如下：

CREATE OR REPLACE FUNCTION hr.person()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- 若业务允许grade为NULL，放开下一行的注释即可
    -- IF NEW.grade IS NULL THEN RETURN NEW; END IF;
    
    -- 校验grade是否存在于合法值表中
    IF NOT EXISTS (
        SELECT 1 
        FROM hr.grade_salary 
        WHERE grade = NEW.grade
    ) THEN
        RAISE EXCEPTION 'Illegal grade value: %, please use valid grade defined in grade_salary table', NEW.grade;
    END IF;

    RETURN NEW;
END;
$$;

额外说明：如果grade_salary.grade是该表的主键或唯一键，上述EXISTS查询的性能极高，不会对写入效率造成明显影响。如果后续合法grade值有调整，只需要维护grade_salary表的数据即可，不需要修改触发器函数逻辑。

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