Postgresql触发器函数监控指定表数据的改变

Postgresql触发器函数监控指定表数据的改变

一、创建存储过程

create or replace function monitor_table_dml() returns trigger
as $monitor_table_dml$
declare
	data_new VARCHAR(255);
	tablename VARCHAR(255);
	action_s VARCHAR(255);
	create_time TIMESTAMP;
begin
--初始化表名
tablename=TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME;
if (TG_OP='DELETE') then
	action_s='Delete';
	data_new=OLD;
elsif (TG_OP='UPDATE') then
	action_s='Update';
	data_new=NEW;
elsif (TG_OP='INSERT') then
	action_s='New';
	data_new=NEW;
end if;
	
-- 更新数据到汇总表（提前创建汇总表factory_abbrev_test_cron，）
<<insert_update>>
loop
	begin
		insert into factory_abbrev_test_cron (
		factory_code,
		factory_name,
		"action",
		create_time)
		values(
		data_new,
		tablename,
		action_s,
		now()
		);
		exit insert_update;
	exception
		when unique_violation then
			-- 什么也不做
	end;
end loop insert_update;
return null;
end;
$monitor_table_dml$ language plpgsql;

二、创建触发器

--自定义名称 monitor_factory_abbrev_test_ddl
--factory_abbrev_test 需要监控的表名称
create trigger monitor_factory_abbrev_test_ddl
after insert or update or delete on factory_abbrev_test
	for each row execute procedure monitor_table_dml();

参考：
https://www.open-open.com/lib/view/open1427082615949.html
https://blog.csdn.net/yxl_1207/article/details/104551095