【数据库】通过触发器实现审计日志记录（记录增、删、改）

深藏阁楼爱情的钟 2021-11-06 02:12 361阅读 0赞

#创建测试表
    CREATE TABLE COMPANY(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL
    );
    
    #变更记录表
    CREATE TABLE "public"."company_log" (
    "id" int4 NOT NULL,
    "name" text COLLATE "default" NOT NULL,
    "age" int4 NOT NULL,
    "address" char(50) COLLATE "default",
    "salary" float4,
    "s_id" int8 DEFAULT nextval('company_log_s_id_seq'::regclass) NOT NULL,
    CONSTRAINT "company_log_pkey" PRIMARY KEY ("s_id")
    )
    WITH (OIDS=FALSE)
    ;
    
    ALTER TABLE "public"."company_log" OWNER TO "postgres";
    #审计日志表
    CREATE TABLE "public"."audit_log" (
    "id" int8 DEFAULT nextval('audit_log_id_seq'::regclass) NOT NULL,
    "table_name" varchar(255) COLLATE "default",
    "operation" varchar(255) COLLATE "default",
    "update_time" text COLLATE "default",
    "table_id" int8,
    CONSTRAINT "audit_log_pkey" PRIMARY KEY ("id")
    )
    WITH (OIDS=FALSE)
    ;
    
    ALTER TABLE "public"."audit_log" OWNER TO "postgres";
    
    
    
    #创建触发器函数
    CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
     DECLARE
      tid INTEGER;
       BEGIN
    			if (TG_OP='DELETE' OR TG_OP='UPDATE' ) THEN
    INSERT INTO company_log (ID,NAME,AGE,ADDRESS,SALARY) VALUES (OLD.id,OLD.name, OLD.age, OLD.address, OLD.salary) RETURNING s_id into tid;
    
      ELSEIF (TG_OP='INSERT') THEN
        INSERT INTO company_log (ID,NAME,AGE,ADDRESS,SALARY) VALUES (NEW.id,NEW.name, NEW.age, NEW.address, NEW.salary) RETURNING s_id into tid;
    
      END IF;
          INSERT INTO audit_log(table_name, operation,update_time,table_id) VALUES ('company',TG_OP, current_timestamp,tid);
          RETURN NEW;
    
    
       END;
    
    $example_table$ LANGUAGE plpgsql;
    
    
    #创建触发器
    create trigger company_trigger
    after insert or update or delete on company
    	for each row execute procedure auditlogfunc();
    #执行测试
    INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (11, 'Paul', 32, 'California', 20000.00 );

实现效果

company表

![1619281-20190808143649652-396550061.png][]

触发更新的company\_log表

![1619281-20190808143330553-1497213407.png][]

审计日志表 audit_log

![1619281-20190808145515163-1296534631.png][]

以上周只是简单的实现，比如如何在触发器中拿到登录的用户和ip信息，确定是当前某个用户的操作，这还是一个问题

后续会做更新

转载于:https://www.cnblogs.com/mrwh/p/11321200.html

[1619281-20190808143649652-396550061.png]: /images/20211104/29669b0de9e24bb297b014c5254f4d96.png
[1619281-20190808143330553-1497213407.png]: /images/20211104/66802d907f164a31a0da3f5a892a723b.png
[1619281-20190808145515163-1296534631.png]: /images/20211104/5b891adaad4142a397a856f75e29bbfb.png