Postgresql使用触发器自动插入修改日志（记录），以及自动更新修改时间

Dear 丶 2022-12-26 00:58 293阅读 0赞

### 触发器非常方便，它可以代替部分我们在后台代码里进行的手动操作 ###

我有一个**用户表**，里面有用户的**id**以及**用户名**。  
我还有一个**用户更名日志表**，用于记录**用户名的改动记录**，方便日后追踪。

我希望使用触发器，让**用户名被修改时自动插入用户名改动的日志**！  
日志记录了用户的**id**，**新名字**以及**旧名字**，并且创建记录时**自动记录当前的时间戳**。

#### 并且，我希望使用触发器自动更新记录的修改时间。 ####

--------------------

### 首先要创建用户表： ###

**id自增长，创建时间以及修改时间也是自动的。**

CREATE SCHEMA "user";
    CREATE TABLE "user".base (
        id integer NOT NULL,
        name character varying(255) NOT NULL,
        password character varying(255) NOT NULL,
        create_time timestamp(6) with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
        update_time timestamp(6) with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
    );
    
    CREATE SEQUENCE "user".base_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        MAXVALUE 2147483647
        CACHE 1;
    ALTER SEQUENCE "user".base_id_seq OWNED BY "user".base.id;
    ALTER TABLE ONLY "user".base ALTER COLUMN id SET DEFAULT nextval('"user".base_id_seq'::regclass);

### 然后是用户名修改记录表： ###

CREATE SCHEMA user_records;
    CREATE TABLE user_records.rename (
        id integer NOT NULL,
        user_id integer NOT NULL,
        new_name character varying(255) NOT NULL,
        old_name character varying(255) NOT NULL,
        create_time timestamp(0) with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
    );
    
    CREATE SEQUENCE user_records.rename_records_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        MAXVALUE 2147483647
        CACHE 1;
    ALTER SEQUENCE user_records.rename_records_id_seq OWNED BY user_records.rename.id;
    ALTER TABLE ONLY user_records.rename ALTER COLUMN id SET DEFAULT nextval('user_records.rename_records_id_seq'::regclass);

### 创建触发器的语句： ###

CREATE TRIGGER auto_rename_record BEFORE UPDATE ON "user".base 
    	FOR EACH ROW EXECUTE PROCEDURE "user".auto_rename_record();

### 创建触发器函数的代码： ###

CREATE FUNCTION "user".auto_rename_record() RETURNS trigger
        LANGUAGE plpgsql
        AS $$BEGIN
    		--只有update的时候有OLD，所以必须判断操作类型为UPDATE
    		IF (TG_OP = 'UPDATE') THEN
    			--修改记录的update时间为当前的时间戳
    			NEW.update_time = CURRENT_TIMESTAMP;
    			--如果用户名被修改了，就插入到日志，并记录新、旧名字
    			IF OLD.name <> NEW.name THEN
    				INSERT INTO "user_records"."rename" ("user_id", "new_name", "old_name") 
    				VALUES (NEW.id, NEW.name, OLD.name);
    			END IF;
    		END IF;
    	RETURN NEW;
    END$$;

这样处理后，当我们直接修改用户表中的用户名时，会自动更新update\_time，并插入更名日志到user\_records表中。

搞定了