在BEFORE触发器中通过NEW修改字段后，如何触发AFTER触发器？

核心现象：在PostgreSQL中，当通过BEFORE触发器里的NEW对象修改字段值时，仅监听该字段更新的AFTER触发器不会被触发。比如以下场景，修改etime触发BEFORE触发器计算cost，但监听cost更新的AFTER触发器并未执行。

示例代码 #

1. 创建测试表 #

CREATE TABLE IF NOT EXISTS table1 (
    id serial PRIMARY KEY,
    parent_id integer,
    stime timestamptz NOT NULL DEFAULT NOW(),
    etime timestamptz DEFAULT NULL,
    time_spent real DEFAULT NULL,
    rate NUMERIC(20, 4) DEFAULT NULL,
    cost NUMERIC(20, 4) DEFAULT NULL
);

2. BEFORE触发器（计算cost字段） #

CREATE OR REPLACE FUNCTION trigger1()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RAISE NOTICE 'trigger1{%, %, %, %} runned!', TG_WHEN, TG_OP, TG_TABLE_NAME, COALESCE(NEW.id, OLD.id);
    NEW.time_spent = EXTRACT(EPOCH FROM (NEW.etime - NEW.stime));
    NEW.cost := NEW.time_spent * NEW.rate; -- 此处修改了cost字段
    RETURN NEW;
END;
$BODY$;
DROP TRIGGER IF EXISTS before_100_calc_cost ON table1;
CREATE TRIGGER before_100_calc_cost 
  BEFORE INSERT OR UPDATE OF stime, etime ON table1 
  FOR EACH ROW EXECUTE PROCEDURE trigger1();

3. AFTER触发器（监听cost更新，但未触发） #

CREATE OR REPLACE FUNCTION trigger2()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RAISE NOTICE 'trigger2{%, %, %, %} runned!', TG_WHEN, TG_OP, TG_TABLE_NAME, COALESCE(NEW.id, OLD.id);
    create table if not exists debug_(
      id int generated by default as identity primary key
      , ts timestamptz default clock_timestamp()
      , comment text);
    insert into debug_(comment)
      values(format('tg_op:"%s",new:"%s",old:"%s"'
                    ,tg_op,to_jsonb(new)::text,to_jsonb(old)::text));
    IF (TG_OP <> 'DELETE') THEN RETURN NEW; ELSE RETURN OLD; END IF;
END;
$BODY$;
DROP TRIGGER IF EXISTS after_105_calc_parent_cost ON table1;
CREATE TRIGGER after_105_calc_parent_cost 
  AFTER UPDATE OF cost ON table1 
  FOR EACH ROW EXECUTE PROCEDURE trigger2();

测试案例 #

INSERT INTO table1 (stime) VALUES (NOW() - INTERVAL '1 hour');
UPDATE table1 SET etime = NOW(); -- 执行后trigger2并未触发

解决方案 #

PostgreSQL的触发器机制中，UPDATE OF column的触发条件是该字段在原始UPDATE语句中被指定修改，而非BEFORE触发器中通过NEW对象修改的情况。以下是三种可行方案：

方案1：修改AFTER触发器的触发条件，去掉字段限制 #

将AFTER触发器改为监听所有UPDATE操作，再在函数内部判断cost字段是否发生变化：

DROP TRIGGER IF EXISTS after_105_calc_parent_cost ON table1;
CREATE TRIGGER after_105_calc_parent_cost 
  AFTER UPDATE ON table1 
  FOR EACH ROW EXECUTE PROCEDURE trigger2();

同时在trigger2函数开头添加判断逻辑：

IF NEW.cost = OLD.cost THEN RETURN NEW; END IF; -- 若cost未变化则直接返回，不执行后续逻辑

方案2：在BEFORE触发器中手动触发更新（不推荐） #

可以在trigger1中执行UPDATE table1 SET cost = NEW.cost WHERE id = NEW.id;，但这种方式会额外产生一次UPDATE操作，若AFTER触发器又修改字段，可能引发循环触发，需谨慎使用。

方案3：合并触发器逻辑（最优） #

如果业务逻辑允许，将trigger2的逻辑整合到trigger1中，避免依赖字段更新触发的AFTER触发器，减少触发器层级，提升执行效率。

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