MelonBlog

在postgresql中通过触发器实现“更新时间”字段

postgresql中没有mysql的ON UPDATE CURRENT_TIMESTAMP功能，想要实现当一行记录更新的时候，更新时间字段能够自动更新为当前时间，必须用到postgresql的触发器功能

在postgresql中创建触发器，要做2件事情：

创建触发器函数

将触发器函数与指定的表关联

触发器分为：

DDL触发器（事件触发器）

DML触发器（数据变更触发器）

大部分情况下，只会用到DML触发器，例如，当我们想要自动更新updated_at字段时，就会用到DML触发器。

触发器的4个时机：

BEFORE：在触发事件（如 INSERT、UPDATE、DELETE）实际执行之前触发。

AFTER：在触发事件完成后触发。

INSTEAD OF：仅用于视图（View），替代触发事件执行。

CONDITIONAL（条件触发）：在 PostgreSQL 14+ 中支持，通过 WHEN 子句指定触发条件。

自动更新时间场景下，会用到 BEFORE 这个时机

创建触发器函数的标准语法：

CREATE OR REPLACE FUNCTION 函数名()

RETURNS TRIGGER AS $$

DECLARE

    -- 声明变量（可选）

BEGIN

    -- 函数逻辑

    RETURN NEW | OLD | NULL;

END;

$$ LANGUAGE plpgsql;

当我们要创建一个自动更新updated_at字段的触发器函数时，要这样写：

-- 创建通用的自动更新时间戳函数

CREATE OR REPLACE FUNCTION set_updated_at()

    RETURNS TRIGGER AS $$

BEGIN

    NEW.updated_at = NOW();

    RETURN NEW;

END;

$$ LANGUAGE plpgsql;

触发器函数有一些特殊的变量，例如上面的NEW，代表更新之后的记录

下面列出来常用的一些特殊变量：

NEW ，类型为 RECORD，代表了行级触发器 INSERT、UPDATE 操作之后的新数据行。对于 DELETE 操作或者语句级触发器而言，该变量为 null；

OLD，类型为 RECORD，代表了行级触发器 UPDATE、DELETE 操作之前的旧数据行。对于 INSERT 操作或者语句级触发器而言，该变量为 null；

TG_NAME，触发器的名称；

TG_WHEN，触发的时机，例如 BEFORE、AFTER 或者 INSTEAD OF；

TG_LEVEL，触发器的级别，ROW 或者 STATEMENT；

TG_OP，触发的操作，INSERT、UPDATE、DELETE 或者 TRUNCATE；

TG_RELID，触发器所在表的 oid；

TG_TABLE_NAME，触发器所在表的名称；

TG_TABLE_SCHEMA，触发器所在表的模式；

TG_NARGS，创建触发器时传递给触发器函数的参数个数；

TG_ARGV[]，创建触发器时传递给触发器函数的具体参数，下标从 0 开始。非法的下标（小于 0 或者大于等于 tg_nargs）将会返回空值。

标准语法：

CREATE [ CONSTRAINT ] TRIGGER 触发器名称

{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }

ON 表名

[ FROM referenced_table_name ]

[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]

[ FOR [ EACH ] { ROW | STATEMENT } ]

[ WHEN ( condition ) ]

EXECUTE FUNCTION 函数名 ( arguments );

当我们要把刚创建的set_updated_at函数关联到users表中时，这样写：

-- 为指定表创建自动更新触发器

CREATE TRIGGER update_timestamp

    BEFORE UPDATE ON users

    FOR EACH ROW -- 对每一行更新操作执行一次

EXECUTE FUNCTION set_updated_at();

创建完触发器函数set_updated_at，并将它关联到指定的表之后，该表就可以自动更新updated_at字段了。