PostgreSQL表层级时间自动更新

一、实现思路

对于很多业务表，最常用以下几个字段：创建时间（create_at） 、更新时间（update_at）、创建人 （create_by）、更新人（update_by）通常在业务上有两种更新方式：

1. 通过实现HandlerInterceptor自定义拦截器获用户信息和当前时间，在持久层对公共实体类进行统一更新保存
2. 在数据库层级更新公共字段，但只仅限于时间这里不能实现用户信息的更新

对于MySQL来说，其内部提供的函数对于创建时间和更新时间的字段的自动更新是相当容易的，但对于PostgreSQL事情会稍稍复杂一点。

二、实现步骤

要在插入数据的时候自动填充 create_at列的值，我们可以使用DEFAULT值，如下面所示。

CREATE TABLE users (
  ...
  create_at timestamp(6) default current_timestamp
)

设置create_at字段默认为current_timestamp即当前时间戳，这样可以在 INSERT 语句中自动默认为当前时间戳。以上操作只能实现插入数据更新时间字段，要实现create_at字段自动UPDATE就需借助数据库触发器即Trigger，也可将触发器理解成自定义的SQL函数。

补充：触发器（Trigger）是由事件自动触发执行的一种特殊的存储过程，触发事件可以是对一个表进行INSERT、UPDATE、DELETE等操作。触发器经常用于加强数据的完整性约束和业务规则上的约束等。

-- 创建触发器
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
	#需要更新字段
    NEW.update_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

触发器该如何使用？上面设置触发器名称为update_modified_column()，对我们需要做时间戳更新操作的表设置相应的触发器即可，只需触发器名称和表明替换。需要给每一个做类似操作的表都增加触发器，因此这种方法适用于小规模，区别于全局更细的特殊逻辑场景。

 -- 设置触发器
CREATE TRIGGER trigger_name BEFORE 
UPDATE ON table_name 
FOR EACH ROW EXECUTE PROCEDURE update_modified_column();

-- 删除触发器
DROP TRIGGER update_table_name_update_at ON sms001_user_info ;