利用触发器设置PostgreSQL表监控:监控表的增,删,改

1.创建logging模式,并且创建一个logging模式下的记录表:

CREATE SCHEMA logging;

CREATE TABLE logging.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  );

2.创建触发器函数:

CREATE FUNCTION change_user_trigger()      RETURNS trigger AS $$      BEGIN          IF TG_OP = 'INSERT' THEN              INSERT  INTO logging.user_history(tabname,schemaname,operation,new_val)  values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP,row_to_json(NEW));          RETURN NEW;          ELSIF TG_OP = 'UPDATE'  THEN                  INSERT INTO logging.user_history(tabname,schemaname,operation,new_val,old_val) values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP,row_to_json(NEW),row_to_json(OLD));          RETURN NEW;          ELSIF TG_OP = 'DELETE'  THEN                INSERT INTO logging.user_history(tabname,schemaname,operation, old_val) values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP,row_to_json(OLD));   RETURN OLD; --返回值要与ELSIF平齐,因为先插入后最好才执行返回   END IF;  END;  $$ LANGUAGE 'plpgsql' SECURITY DEFINER;--security definer是指定创建该函数用户的权限执行,security invoker是指以调用该函数用户发权限执行

3.创建测试表users:

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

4.创建触发器:

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

5.测试：

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

这个函数的缺陷:

1,它不能监控select语句

2,它不能监控系统表

3,它不能监控DDL操作,例如alter table(如果需要监控可以配置postgresql.conf文件,修改log_statment参数:#log_statement = 'none'    # none, ddl, mod, all)