PostgreSQL 15.4更新触发器问题求助：行级更新与性能疑问

一、修复触发器问题并实现需求 #

1. 推荐方案：使用BEFORE UPDATE触发器（高效无额外更新） #

你第一个触发器报错是因为WHEN子句缺少括号，且AFTER触发器不适合直接修改行（会引发递归触发或额外UPDATE操作）。推荐用BEFORE触发器直接修改NEW行的日期列，无需执行UPDATE语句，性能更优：

先创建触发器函数：

CREATE OR REPLACE FUNCTION fnupdate_functionname() 
RETURNS TRIGGER 
AS $BODY$
BEGIN
  -- 仅当布尔列发生变化时更新日期列，用IS DISTINCT FROM处理NULL场景
  IF OLD.columnname IS DISTINCT FROM NEW.columnname THEN
    NEW.columnname_utc = CURRENT_TIMESTAMP AT TIME ZONE 'utc';
  END IF;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

再创建触发器（修正语法错误）：

CREATE OR REPLACE TRIGGER trpopulate_tablecolumn
BEFORE UPDATE OF columnname ON tablename
FOR EACH ROW    
WHEN (OLD.columnname IS DISTINCT FROM NEW.columnname) -- 必须加括号，避免语法错误
EXECUTE FUNCTION fnupdate_functionname();

2. 若坚持使用AFTER UPDATE触发器（不推荐） #

如果必须用AFTER触发器，需要通过主键精准定位当前受影响行（假设表主键为id），避免更新全表：

CREATE OR REPLACE FUNCTION fnupdate_functionname() 
RETURNS TRIGGER 
AS $BODY$
BEGIN
  IF OLD.columnname IS DISTINCT FROM NEW.columnname THEN
    UPDATE tablename 
    SET columnname_utc = CURRENT_TIMESTAMP AT TIME ZONE 'utc'
    WHERE id = NEW.id; -- 用主键锁定当前行，避免批量更新
  END IF;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

触发器定义修正语法错误：

CREATE OR REPLACE TRIGGER trpopulate_tablecolumn
AFTER UPDATE OF columnname ON tablename
FOR EACH ROW    
WHEN (OLD.columnname IS DISTINCT FROM NEW.columnname)
EXECUTE FUNCTION fnupdate_functionname();

二、关于FOR EACH ROW的性能疑问 #

FOR EACH ROW触发器只会对被UPDATE语句实际影响的行触发，而非表中所有行：

• 若你的UPDATE语句修改了10行数据，触发器就触发10次，每次处理一行
• 若UPDATE语句没有匹配到任何行，触发器不会触发

只要触发器函数逻辑高效（比如推荐方案里直接修改NEW行，无额外UPDATE操作），即使表行数多，也不会有严重性能问题。

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