PostgreSQL PL/pgSQL中FOR EACH STATEMENT触发器与FOR EACH ROW AFTER触发器执行控制问题咨询

嘿，我来帮你理清这两个PL/pgSQL触发器的困惑点，这确实是学习触发器时容易卡壳的地方！

1. FOR EACH STATEMENT触发器如何控制表的INSERT/UPDATE/DELETE操作？ #

首先得明确：FOR EACH STATEMENT触发器的函数只能返回NULL，没法像FOR EACH ROW BEFORE触发器那样通过返回NEW/OLD/NULL来控制单条行的执行逻辑。如果要阻止整个DML语句的执行，你需要在触发器函数中主动抛出异常（RAISE EXCEPTION），这样PostgreSQL会中断当前语句，并且不会执行任何对表的修改。

举个简单的例子，假设我们要禁止在工作日的非工作时间执行更新操作：

CREATE OR REPLACE FUNCTION block_off_hours_updates()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查当前时间是否在非工作时段（周一到周五的18点后或8点前）
    IF EXTRACT(DOW FROM CURRENT_TIMESTAMP) BETWEEN 1 AND 5 
       AND (EXTRACT(HOUR FROM CURRENT_TIMESTAMP) < 8 OR EXTRACT(HOUR FROM CURRENT_TIMESTAMP) >= 18) THEN
        RAISE EXCEPTION '更新操作仅允许在工作日8:00-18:00执行';
    END IF;
    RETURN NULL; -- STATEMENT级触发器必须返回NULL
END;
$$ LANGUAGE plpgsql;

-- 创建BEFORE STATEMENT级触发器
CREATE TRIGGER prevent_off_hours_updates
BEFORE UPDATE ON your_table
FOR EACH STATEMENT
EXECUTE FUNCTION block_off_hours_updates();

当触发器函数抛出异常时，整个UPDATE语句会被终止，表不会有任何修改。需要注意的是，只有BEFORE STATEMENT触发器能起到提前阻止作用，AFTER STATEMENT触发器触发时，DML语句已经执行完毕了，这时候抛出异常只会回滚整个事务，但没法阻止初始的修改（不过回滚后最终结果还是没修改）。

2. FOR EACH ROW AFTER触发器如何实现回滚操作？ #

FOR EACH ROW AFTER触发器是在单条行的INSERT/UPDATE/DELETE操作完成后触发的，此时该行已经被修改。如果要回滚这个操作（甚至整个事务），同样只能通过抛出异常来实现——因为PostgreSQL中一旦触发器函数抛出异常，整个当前事务会被回滚，所有已执行的修改都会被撤销。

比如，我们要确保插入的用户年龄不能小于18，在AFTER触发器中检查并回滚：

CREATE OR REPLACE FUNCTION check_adult_age_after()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.age < 18 THEN
        RAISE EXCEPTION '插入的用户年龄必须大于等于18岁，已回滚操作';
    END IF;
    RETURN NULL; -- AFTER ROW触发器必须返回NULL
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER verify_adult_age_after
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION check_adult_age_after();

当插入一条年龄小于18的记录时，AFTER触发器触发后抛出异常，整个INSERT操作会被回滚，这条记录不会被保存到表中。

这里要提醒一下：虽然AFTER触发器能通过异常回滚，但更合理的做法其实是用FOR EACH ROW BEFORE触发器直接阻止插入（返回NULL或抛出异常），因为AFTER触发器是在修改完成后再触发，会多一次磁盘写入的开销，性能不如BEFORE触发器。不过如果你的业务逻辑必须依赖行已经被插入后的状态（比如关联其他表的操作后再检查），那AFTER触发器+异常回滚就是可行的方案。

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