怎样在PostgreSQL中使用SQL触发器自动填充更新时间戳？-SQL-PHP中文网

postgresql不支持on update current_timestamp，default仅对insert生效，update时必须用before触发器函数（如new.updated_at = now(); return new）自动更新；仅建触发器不写函数或漏return new将失效。

怎样在postgresql中使用sql触发器自动填充更新时间戳？

PostgreSQL 触发器能自动更新 updated_at 字段，但必须配对使用触发器函数，不能只建触发器；直接在 UPDATE 语句里写 NOW() 不可靠，因为可能被绕过。

为什么不能只用 `DEFAULT NOW()` 或 `ON UPDATE CURRENT_TIMESTAMP`

PostgreSQL 没有 MySQL 那样的 ON UPDATE CURRENT_TIMESTAMP 语法，DEFAULT NOW() 只在 INSERT 时生效，UPDATE 时不会自动重置。如果业务代码漏写 updated_at = NOW()，字段就 stale 了。

DEFAULT NOW() 仅作用于 INSERT 缺失该列值时
显式设为 NULL 或其他值会跳过默认值
应用层手动更新容易遗漏、不一致，尤其跨服务或 ORM 自动生成 SQL 时

必须先创建触发器函数，再绑定到表

触发器本身不执行逻辑，只是“开关”，真正干活的是你写的函数。函数必须返回 TRIGGER 类型，并在 UPDATE 时修改 NEW.updated_at。

TTSMaker

TTSMaker是一款支持多语言和多音色的在线文本转语音工具。

下载

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

函数名任意，但建议见名知意，比如 update_updated_at_column
NEW 是即将写入的新行，赋值给它才生效；RETURN NEW 必不可少
用 plpgsql 而非 sql 语言，因为 sql 函数无法修改 NEW
如果表已有数据，记得补全历史记录：UPDATE table_name SET updated_at = created_at WHERE updated_at IS NULL;

为表添加触发器时注意触发时机和条件

触发器需指定在 UPDATE 事件发生时执行，并建议加 WHEN 条件避免无谓调用。

CREATE TRIGGER update_updated_at_column 
  BEFORE UPDATE ON users 
  FOR EACH ROW 
  WHEN (OLD.* IS DISTINCT FROM NEW.*) 
  EXECUTE FUNCTION update_updated_at_column();

BEFORE UPDATE：确保在写入前完成时间戳更新，避免竞态
FOR EACH ROW：逐行触发，不是按语句（STATEMENT）
WHEN (OLD.* IS DISTINCT FROM NEW.*)：跳过空更新（如 UPDATE users SET name=name WHERE id=1），提升性能
若只希望某些列变更时更新时间戳，可改用 WHEN (OLD.col1 IS DISTINCT FROM NEW.col1 OR OLD.col2 IS DISTINCT FROM NEW.col2)

常见错误和兼容性注意点

实际部署时最常卡在这几个地方：

忘记 RETURN NEW → 触发器静默失败，updated_at 不变，且无报错
函数语言写成 language sql → 报错 cannot modify NEW
触发器命名重复 → ERROR: trigger "xxx" for relation "yyy" already exists，可用 DROP TRIGGER IF EXISTS 预防
迁移脚本中未处理已存在函数/触发器 → 建议加 CREATE OR REPLACE FUNCTION 和 DROP TRIGGER IF EXISTS
PostgreSQL 9.6+ 支持 EXECUTE FUNCTION，旧版本需用 EXECUTE PROCEDURE