利用觸發器設置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)