postgresql触发器应用-INSERT-UPDATE-数据表版本表应用

weixin_44847617

于 2024-03-29 15:48:18 发布

阅读量672

点赞数 9

文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/weixin_44847617/article/details/137142844

版权

1.实现功能

主要是给数据库表的数据做数据备份使用,

2.设计实现

给数据表加上两张表，后缀名his，log，为plt_cus_studenthis，plt_cus_studentlog，his表（历史表）主要记录该表原数据，log表（日志表）主要记录数据操作日志

案例表

历史表：plt_versionid,历史表主键id，确定唯一数据唯一id，plt_dataversion，某条数据版本号，记录数据多次变更记录，属性名得取dataversion而不是version，怕原表已经有version属性，引起冲突。

日志表：plt_oid是记录原表数据id，plt_dataversion是操作数据版本，这两个字段可以确定历史表中哪条数据的操作记录，plt_operationlog记录修改了那些字段信息，plt_updatetime修改时间

3.具体实现

3.1创建触发器函数

create or replace function plt_cus_student_update()
returns trigger
AS $$
declare
  version_max integer; 
  operation_log text;
begin
  if tg_op = 'UPDATE' then 
  	operation_log = '';
    SELECT MAX(plt_dataversion) into version_max FROM plt_cus_studenthis where id = old.id;
	if version_max is NULL then version_max=0;
	else
	version_max = version_max+1;
	end if;
	if version_max = 0 then INSERT INTO public.plt_cus_studenthis(id, name, age, plt_dataversion)VALUES (old.id, old.name, old.age, 0);
    else
	INSERT INTO public.plt_cus_studenthis(id, name, age, plt_dataversion)VALUES (old.id, old.name, old.age, version_max);
  	end if;
	if old.name!=new.name then operation_log = operation_log || '修改了name字段,';
	end if;
	if old.age!=new.age then operation_log = operation_log || '修改了age字段,';
	end if;	
	
	INSERT INTO public.plt_cus_studentlog(plt_oid, plt_operationlog, plt_updatetime,plt_dataversion)VALUES ( old.id, operation_log, current_timestamp,version_max);
  end if;	
  return new;
end $$
language plpgsql;

备注：version_max，是查看该条数据是否有版本，没有就设当前版本为0，|| 运算符是字符串拼接

3.2给表的update的事件触发器绑定函数

create trigger plt_cus_student_change
before UPDATE
on plt_cus_student
for each row
execute function plt_cus_student_update();

3.3查看表的触发器

SELECT tgname, tgisinternal, tgtype, tgrelid::regclass, tgfoid::regproc, tgattr, tgqual, tgdeferrable, tginitdeferred, tgnargs, tgargs
FROM pg_trigger
WHERE tgrelid = 'plt_cus_student'::regclass;