postgresql触发器记录更新日志

1. 创建日志表

CREATE TABLE t_audit_log(
	id serial,
	ip varchar,   				-- 修改人所对应ip
	username text, 				-- 修改人 
	event_time_utc timestamp, 	-- 修改时间
	table_name text,		 	-- 修改的表名 
	operation text, 			-- INSERT, UPDATE, DELETE or TRUNCATE 
	before_value json, 			-- 修改前的数据
	after_value json 			-- 修改后的数据
);

2. 创建获取ip的函数

CREATE OR REPLACE FUNCTION get_client_ip() RETURNS text AS $$
DECLARE
    client_ip text;
BEGIN
    SELECT inet_client_addr() INTO client_ip;
    RETURN client_ip;
END;
$$ LANGUAGE plpgsql;

3.创建触发器函数

CREATE OR REPLACE FUNCTION audit_trigger() RETURNS trigger AS $$ 
  DECLARE 
   old_row json := NULL; 
   new_row json := NULL; 
  BEGIN
   IF TG_OP IN ('UPDATE','DELETE') 
   THEN
     old_row = row_to_json(OLD);
   END IF; 
   IF TG_OP IN ('INSERT','UPDATE') 
   THEN
     new_row = row_to_json(NEW);
     END IF; 
   INSERT INTO t_audit_log(ip,username, event_time_utc, table_name, operation, before_value, after_value ) 
   VALUES (get_client_ip(),session_user, current_timestamp AT TIME ZONE 'UTC', TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, TG_OP, old_row, new_row ); 
   RETURN NEW; 
   END;
$$ LANGUAGE plpgsql; 

4. 创建触发器

CREATE TRIGGER insert_customer_trigger -- 触发器名称
BEFORE INSERT ON table_name --表名
FOR EACH ROW --触发时机，这里按行
EXECUTE FUNCTION insert_customer_if_not_exists(); -- 执行的函数