PostgreSQL AFTER语句级触发器疑问及表级约束实现咨询

1. AFTER语句级触发器中查询触发表的结果 #

PostgreSQL 中，AFTER INSERT OR UPDATE FOR EACH STATEMENT 触发器是在数据修改操作完全生效后才触发执行的。因此，在触发器函数内查询触发表时，看到的是本次修改后的最终数据——和你提到的MSSQL逻辑相反，不需要关联类似deleted/inserted的虚拟表来获取修改后状态，直接查询原表即可拿到最新结果。

2. 实现表级约束的具体方案 #

这类表级约束（总和限制、布尔列数量限制）属于事务级的全局验证，用语句级AFTER触发器（或约束触发器）就能实现，无需复杂的关联逻辑，以下是具体示例：

示例1：限制某列总和不超过指定值 #

假设要限制accounts表的balance列总和不超过10000：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_total_balance()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT SUM(balance) FROM accounts) > 10000 THEN
        RAISE EXCEPTION '账户总余额不能超过10000';
    END IF;
    RETURN NULL; -- 语句级触发器无需返回行数据
END;
$$ LANGUAGE plpgsql;

-- 创建约束触发器（适配事务内多次修改场景，避免中间状态触发错误）
CREATE CONSTRAINT TRIGGER trigger_check_total_balance
AFTER INSERT OR UPDATE OF balance ON accounts
DEFERRABLE INITIALLY DEFERRED
FOR EACH STATEMENT
EXECUTE FUNCTION check_total_balance();

使用CONSTRAINT TRIGGER并设置DEFERRABLE INITIALLY DEFERRED，可让约束验证延迟到事务提交时执行，允许你在事务内多次调整数据，只要最终状态符合约束即可。

示例2：限制布尔列为true的记录数量 #

假设要限制products表中is_featured = true的记录最多3条：

CREATE OR REPLACE FUNCTION check_max_featured()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(*) FROM products WHERE is_featured = true) > 3 THEN
        RAISE EXCEPTION '最多只能设置3个推荐商品';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_check_max_featured
AFTER INSERT OR UPDATE OF is_featured ON products
DEFERRABLE INITIALLY DEFERRED
FOR EACH STATEMENT
EXECUTE FUNCTION check_max_featured();

补充说明 #

• 语句级触发器会在整个INSERT/UPDATE语句执行完毕后触发一次，适合全局统计类的验证；
• 若为行级规则验证（比如单条记录的字段格式限制），才需要用到NEW/OLD变量；
• 并发场景下，若担心多个事务同时修改导致约束失效，可以在触发器函数中添加行锁，比如用SELECT SUM(balance) FROM accounts FOR UPDATE锁定全表（需根据业务场景权衡性能影响）。

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