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

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

我有一个用户表，里面有用户的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表中。

搞定了