#创建测试表
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表
触发更新的company_log表
审计日志表 audit_log
以上周只是简单的实现,比如如何在触发器中拿到登录的用户和ip信息,确定是当前某个用户的操作,这还是一个问题
后续会做更新