postgresql 触发器如何生成递增序列号，从1开始，并且每天重置

大家好，我是三叔，许久不见，这期给大家介绍一下笔者在开发中遇到的业务处理：pgsql 创建触发器生成每日递增序列，并且第二天重置，根据不同的用户进行不同的控制。

1.创建生成递增序列的 table 表

-- 创建user_sequences表，用于保存生成的序列号
CREATE TABLE user_sequences 
(id                        serial,user_name                 varchar(255) NULL,                           -- 区分不同用户（可根据不同业务组装key）increment_value           int4 NULL,                                   -- 递增序列号last_increment_date       timestamp NULL,                              -- 触发器参数时间description				  varchar(255) NULL,						   -- 描述tenant_id                 varchar(32) NULL,                            -- 租户IDcreate_user               varchar(32) NULL,                            -- 创建人IDcreate_time               timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间update_user               varchar(32) NULL,                            -- 最后修改人IDupdate_time               timestamp NULL DEFAULT CURRENT_TIMESTAMP,    -- 更新时间CONSTRAINT user_sequences_pk PRIMARY KEY (id)
);

2.创建一个触发器函数

要创建一个触发器，你需要：
1、使用 create function 语句创建一个触发器函数
2、使用 create trigger 语句将触发器函数绑定到数据表
3、创建触发器函数语法：
触发器函数与普通函数类似，不同之处在于触发器不需要任何参数，并且触发器函数需要返回trigger类型值

-- 创建触发器函数
CREATE OR REPLACE FUNCTION insert_increment_value_if_not_exists()
RETURNS TRIGGER AS $$
BEGIN-- 检查 user_sequences 表中是否存在对应的 user_nameIF NOT EXISTS (SELECT 1 FROM user_sequences WHERE user_name = NEW.user_name) THEN-- 在 user_sequences 表中插入新的记录RETURN NEW;else 	 UPDATE user_sequencesSET increment_value = increment_value + 1WHERE user_name = NEW.user_name; END IF;RETURN null;
END;
$$ LANGUAGE plpgsql;

上述部分语法解释

NEW：该变量为 INSERT/UPDATE 操作触发的行级触发器中存储新的数据行

3.创建触发器

-- 这里介绍一下创建触发器语法(如下简单语法)：
create trigger trigger_name{before | after | instead of} event1 or event2...on table_name{for each row | for each statement}execute procedure trigger_function;event1： 事件可以是：insert, update, delete, truncate
before： 表示定义触发器在事件之前
after:   表示定义触发器在事件之后
intead of：   表示仅用于视图的insert, update, delete
for each row：表示行级触发器
for each statement：表示语级触发器

实际业务逻辑如下：笔者在插入表数据的时候触发执行器执行begin -》end 的 sql 逻辑，这样，我就能在每天对不同的用户，生成一个从1开始的递增序列，每次相同用户同一天访问一次，序列递增加1：

CREATE TRIGGER insert_increment_value_trigger
BEFORE INSERT ON user_sequences
FOR EACH ROW
EXECUTE FUNCTION insert_increment_value_if_not_exists();

4.业务处理逻辑

大家可以看到，在创建触发器的时候，有这么一句语法：BEFORE INSERT ON user_sequences，这句话是什么意思呢？
根据上面的触发器语法介绍可以知道，在每次执行 insert 语句之前，会执行触发器里面的语法。

@Service
public class xxxCommon {@Autowiredprivate TableMapper mapper;@Transactional(rollbackFor = Exception.class)public String getSequencesNum(String userName) {// 需要注意的是这里的userName需要保证唯一，可以拼接时间yyyyMMdd，这样可以保证每天获取新的递增序列// 第二种方法，就是在笔者的基础上，添加时间的判断条件，判断上一次修改或生成数据的时间是否不等于当前时间，如果是的，则重新从1开始递增UserSequencesVO vo = new UserSequencesVO();assembleParam(vo);vo.setIncrementValue(1);vo.setUserName(userName);// 触发器触发条件，insert 语句进行触发，这里我在xml里面写了一条新增的sql语句，从而触发触发器mapper.insertTriger(vo);// 这里获取数据库返回的序列号，UserSequencesVO sequencesVO = UserSequencesMapper.selectByUserName(userName);// 根据自己的业务组装返回值return sequencesVO.getIncrementValue();}private void assembleParam(UserSequencesVO vo) {vo.setDescription("获取当前key的递增序列");vo.setTenantId(userUtil.getTenantId());vo.setLastIncrementDate(LocalDateTime.now());vo.setCreateTime(LocalDateTime.now());vo.setCreateUser(userUtil.getId());vo.setTenantId(userUtil.getTenantId());vo.setUpdateTime(LocalDateTime.now());vo.setUpdateUser(userUtil.getTenantId());}
}

业务处理

在拿到递增的序列号之后，就可以对序列号进行一系列的组装操作，这样，根据不同的用户，互不影响，获取从 1 开始递增的序列号，并且第二天重置

结果如下：

笔者成功获取到每天操作文档的次数：

最后：如何删除触发器

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

参数说明：

IF EXISTS：如果该触发器不存在
name：要移除的刚刚定义的触发器的名称
table_name：定义了该触发器的表的名称
CASCADE：自动删除依赖于该触发器的对象，然后删除所有 依赖于那些对象的对象
RESTRICT：如果有任何对象依赖于该触发器，则拒绝删除它，这是默认值

-- 示例
DROP TRIGGER insert_increment_value_trigger ON user_sequences;