PosgtreSQL触发器监控表的数据操作记录（json格式）

一、创建测试表

创建测试表users、users_t、user_history（监控表数据变动的日志表）

CREATE TABLE users
(
  id serial NOT NULL,
  username character varying(40),
  email character varying(100)
);

CREATE TABLE users_t
(
  id serial NOT NULL,
  username character varying(40),
  email character varying(100),
  age int8
);

CREATE TABLE user_history (
    id serial,
    tstamp timestamp DEFAULT now(),
    schemaname text,
    tabname text,
    operation text,
    who text DEFAULT current_user,
    new_val json,
    old_val json
);

二、创建触发器函数

创建触发器函数

CREATE or replace FUNCTION change_user_trigger()
    RETURNS trigger AS $$
    BEGIN
        IF TG_OP = 'INSERT' THEN
            INSERT  INTO user_history(tabname,schemaname,operation,new_val,operation_ip)  values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP,row_to_json(NEW)
            ,(select client_addr from pg_stat_activity where pid = pg_backend_pid()));
        RETURN NEW;
        ELSIF TG_OP = 'UPDATE'  THEN
                INSERT INTO user_history(tabname,schemaname,operation,new_val,old_val,operation_ip) values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP,row_to_json(NEW),row_to_json(OLD)
                ,(select client_addr from pg_stat_activity where pid = pg_backend_pid()));
        RETURN NEW;
        ELSIF TG_OP = 'DELETE'  THEN
              INSERT INTO user_history(tabname,schemaname,operation, old_val,operation_ip) values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP,row_to_json(OLD)
              ,(select client_addr from pg_stat_activity where pid = pg_backend_pid()));
    RETURN OLD; --返回值要与ELSIF平齐,因为先插入后最好才执行返回
    END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

三、创建触发器

在users、users_t上创建触发器，监控INSERT 、 UPDATE 、 DELETE操作

CREATE TRIGGER "logging_user_change"
  BEFORE INSERT OR UPDATE OR DELETE
  ON users
  FOR EACH ROW
  EXECUTE PROCEDURE change_user_trigger();

CREATE TRIGGER "logging_users_t_change"
  BEFORE INSERT OR UPDATE OR DELETE
  ON users_t
  FOR EACH ROW
  EXECUTE PROCEDURE change_user_trigger();

四、插入、删除测试数据测试功能

插入、删除测试数据测试功能

INSERT INTO users(username,email) VALUES ( 'hans','hans@qq.com');
UPDATE users SET id = 1, username ='paul';

INSERT INTO users(username,email) VALUES ( 'tzq','tzq@qq.com');
INSERT INTO users(username,email) VALUES ( 'tzq2','tzq2@qq.com');

INSERT INTO users_t(username,email,age) VALUES ( 'tzq2','tzq2@qq.com',18);
INSERT INTO users_t(username,email,age) VALUES ( 'tzq3','tzq3@qq.com',19);
INSERT INTO users_t(username,email,age) VALUES ( 'tzq4','tzq4@qq.com',20);
delete from users where id = 3;
select * from users;

五、插入监控日志表数据，查看结果

插入监控日志表数据，查看结果

select * from user_history;