如何在SQL中编写触发器自动填充创建时间与修改时间？-SQL-PHP中文网

MySQL用BEFORE INSERT/UPDATE触发器填created_at和updated_at；PostgreSQL用DEFAULT加触发器；SQL Server推荐DEFAULT+ AFTER UPDATE触发器；跨库需注意时区与语法差异。

如何在sql中编写触发器自动填充创建时间与修改时间？

MySQL 中用 BEFORE INSERT 和 BEFORE UPDATE 实现时间戳自动填充

MySQL 触发器是填 created_at 和 updated_at 最直接的方式，尤其当你不能或不想依赖应用层逻辑时。关键在于：必须用 BEFORE 类型触发器，且对 NEW.created_at 和 NEW.updated_at 赋值——AFTER 触发器无法修改当前行数据。

常见错误是写成 AFTER INSERT，结果字段仍是 NULL 或默认 0000-00-00；另一个坑是没给 updated_at 设置 ON UPDATE CURRENT_TIMESTAMP，导致后续手动 UPDATE 不更新时间。

确保字段定义为 DATETIME 或 TIMESTAMP，推荐 TIMESTAMP（自动处理时区，占用更少）
created_at 在 BEFORE INSERT 中设为 NOW()；updated_at 在 BEFORE INSERT 和 BEFORE UPDATE 中都设为 NOW()
如果表已有数据，触发器不会回填旧记录，需单独执行 UPDATE table SET created_at = NOW(), updated_at = NOW() WHERE created_at IS NULL

CREATE TRIGGER set_timestamps_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.created_at IS NULL THEN
    SET NEW.created_at = NOW();
  END IF;
  IF NEW.updated_at IS NULL THEN
    SET NEW.updated_at = NOW();
  END IF;
END;
<p>CREATE TRIGGER set_timestamps_before_update
BEFORE UPDATE ON users
FOR EACH ROW
SET NEW.updated_at = NOW();</p>

PostgreSQL 中用 DEFAULT 和触发器组合更稳妥

PostgreSQL 没有 ON UPDATE 语法，纯靠触发器。但别急着写两个触发器——先用 DEFAULT CURRENT_TIMESTAMP 处理 created_at，再用单个触发器管 updated_at，既简洁又避免重复赋值。

容易忽略的是：PostgreSQL 触发器函数必须先创建，再绑定；且函数返回值必须是 NEW（否则更新会失败）。另外，CURRENT_TIMESTAMP 带时区，若业务要求 UTC，得显式写 CURRENT_TIMESTAMP AT TIME ZONE 'UTC'。

字段定义示例：created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
触发器函数中必须写 RETURN NEW;，漏掉这句会导致 UPDATE 后整行变 NULL
如果表有多个时间字段（如 deleted_at），在触发器里加判断：仅当 OLD.updated_at != NEW.updated_at 才重设，避免误覆盖

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ language 'plpgsql';
<p>CREATE TRIGGER update_users_updated_at 
BEFORE UPDATE ON users 
FOR EACH ROW 
EXECUTE FUNCTION update_updated_at_column();</p>

SQL Server 的 INSTEAD OF 触发器不适用，改用 DEFAULT + 计算列

SQL Server 的 INSTEAD OF 触发器会完全接管 INSERT/UPDATE，写起来复杂、性能差，还容易绕过约束。实际更推荐：用 GETDATE() 作为 DEFAULT，再配合计算列或简单触发器补 updated_at。

注意 GETDATE() 返回本地时区，而 SYSDATETIMEOFFSET() 才带时区信息；如果字段类型是 DATETIME2，默认精度是 7 位小数，可能和应用层解析不一致，建议显式写 DATETIME2(3)。

created_at DATETIME2(3) DEFAULT GETDATE() —— 插入时自动填
updated_at DATETIME2(3) 字段留空，用 AFTER UPDATE 触发器更新（因为 SQL Server 允许 AFTER 中改其他字段，但不能改 INSERTED 行本身）
触发器里必须用 UPDATE() 函数判断是否真改了数据，否则每次 UPDATE 都会无条件刷 updated_at，包括只改了个空格

CREATE TRIGGER trg_update_updated_at ON users
AFTER UPDATE
AS
BEGIN
  IF UPDATE(name) OR UPDATE(email) -- 只监控业务字段
    UPDATE u SET updated_at = GETDATE()
    FROM users u
    INNER JOIN inserted i ON u.id = i.id;
END;

跨数据库迁移时时间字段行为差异最易出问题

同一套建表语句，在 MySQL 和 PostgreSQL 里 CURRENT_TIMESTAMP 的默认行为就不同：MySQL 允许 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 一行搞定，PostgreSQL 必须拆成 DEFAULT + 触发器。SQL Server 则连 ON UPDATE 关键字都不支持。

真正麻烦的不是写法，而是测试盲区：比如本地用 SQLite 开发，它没有触发器语法，全靠应用层填充；上线到生产库却忘了配触发器，结果所有 created_at 都是 1970-01-01。还有时区——开发机用系统本地时间，Docker 容器没挂载 /etc/timezone，导致所有 NOW() 返回 UTC 时间，而前端按北京时间渲染，差了 8 小时。