PostgreSQL触发器更新JSONB字段无效果，求排查解决方法

核心问题：触发器类型错误 #

你使用了AFTER INSERT OR UPDATE触发器，这类触发器是在数据已经写入表之后才执行的，此时修改NEW变量不会对已存储的记录产生任何影响。必须改用BEFORE INSERT OR UPDATE触发器，这样才能在数据写入表之前修改NEW.data，让更新后的JSONB字段被正常存储。

修正后的完整代码 #

第一步：更新触发器函数（可选优化，处理NULL场景）

原函数在data字段为NULL时会出现异常，这里改用更简洁且鲁棒的写法：

CREATE OR REPLACE FUNCTION public.update_dates()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  -- 将时间戳合并到data中，若data为NULL则先转为空JSON对象
  NEW.data = COALESCE(NEW.data, '{}'::jsonb) || jsonb_build_object(
    'created_at', NEW.created_at,
    'modified_at', NEW.modified_at
  );
  RETURN NEW;
END;
$function$

第二步：修改触发器类型

DROP TRIGGER IF EXISTS update_dates_trigger ON stories;
CREATE TRIGGER update_dates_trigger
BEFORE INSERT OR UPDATE ON stories
FOR EACH ROW
EXECUTE FUNCTION update_dates();

额外排查步骤 #

如果修正后仍未生效，可以按以下步骤逐一验证：

• 检查时间戳字段的赋值时机：确保created_at和modified_at在当前触发器执行前已经被赋值（比如通过DEFAULT now()或其他前置触发器设置）。可以在函数中添加日志输出验证：

  RAISE NOTICE 'created_at: %, modified_at: %', NEW.created_at, NEW.modified_at;
  

  执行插入/更新操作后，查看PostgreSQL日志确认时间戳是否为有效值。
• 验证触发器状态：执行以下SQL确认触发器是否正常绑定到目标表且处于启用状态：

  SELECT tgname, tgenabled, relname 
  FROM pg_trigger 
  JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid
  WHERE tgname = 'update_dates_trigger';
  

• 检查权限：确保当前用户拥有执行update_dates函数的权限，以及对stories表的修改权限。

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