Postgresql触发器使用实例_pgsql触发器

1. 使用场景

触发器有众多使用场景，其中最常见之一便是在表新增、删除、更新的时候对表进行记录（历史表）

2. 代码实现

2.1 变量说明

2.2 单表创建索引

功能说明

对表msg_file创建索引，在表有新增、删除、更新的时候对数据及操作进行备份(备份到msg_file_record中)，方便回溯数据。

表准备

原始表

CREATE TABLE "public"."msg_file" (
  "message_id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "file_url" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "file_name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "file_size" int8 NOT NULL
)
;
1
2
3
4
5
6
7

记录表

CREATE TABLE "public"."msg_file_record" (
  "message_id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "file_url" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "file_name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "file_size" int8 NOT NULL,
  "data_type" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "update_time" varchar(50) COLLATE "pg_catalog"."default" NOT NULL
)
;
1
2
3
4
5
6
7
8
9

创建触发器逻辑函数

create or replace function msg_file_record_fun() 
returns trigger as $$
	BEGIN
		IF TG_OP = 'INSERT' then
		INSERT INTO "public"."msg_file_record"("message_id", "file_url", "file_name", "file_size", "data_type", "update_time") 
		VALUES (new.message_id, new.file_url, new.file_name, new.file_size, tg_op, to_char(now(),'YYYYMMDD'));
		ELSIF TG_OP = 'UPDATE' then
		INSERT INTO "public"."msg_file_record"("message_id", "file_url", "file_name", "file_size", "data_type", "update_time") 
		VALUES (new.message_id, new.file_url, new.file_name, new.file_size, tg_op, to_char(now(),'YYYYMMDD'));
		ELSIF TG_OP = 'DELETE' then
		INSERT INTO "public"."msg_file_record"("message_id", "file_url", "file_name", "file_size", "data_type", "update_time") 
		VALUES (old.message_id, old.file_url, old.file_name, old.file_size, tg_op, to_char(now(),'YYYYMMDD'));
		END IF;
		return new;
	END;
$$
LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

调用函数创建触发器

-- drop trigger msg_file_trigger on msg_file;
create trigger msg_file_trigger after insert or delete or update on msg_file for each row
execute function msg_file_record_fun();
1
2
3

2.3 批量创建表索引

功能说明

对数据库中所有的业务表进行记录，记录表最后更新的时间

状态记录表

--创建状态表（指标结果表）
create table t_table_record
(
 tabname varchar(128) primary key, --表名
 last_update_date varchar(40), --最后更新日期 YYYYMMDD
 last_update_time bigint  --最后更新时间（绝对秒）
);
1
2
3
4
5
6
7

创建触发器逻辑函数

--触发器执行函数（数据更新），所有触发器都使用这个函数
CREATE OR replace FUNCTION data_update_trigger_fun()
returns trigger as $$
begin
	--更新状态表
	INSERT INTO t_table_record(tabname, last_update_date,last_update_time) VALUES(TG_TABLE_NAME,to_char(now(),'YYYYMMDD'),floor(extract(epoch from now())))
    ON conflict(tabname) DO UPDATE
	set last_update_date = excluded.last_update_date,last_update_time = excluded.last_update_time;
	
	return NULL;
end;
$$
LANGUAGE plpgsql;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

创建索引新增函数

--创建存储过程,用于对数据库里的所有表进行批量创建触发器
CREATE OR replace FUNCTION batch_create_trigger_fun() returns integer as $$
DECLARE
	tablename RECORD;
	--定义游标（!!!!!!!这个地方可以对需要创建触发器的表做筛选，比如只取指标相关的结果表!!!!!!）
	cur_tablename CURSOR FOR select table_name from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE' and table_name not in ('t_table_record');
	--变量：触发器名称
	triggername varchar := '';
begin
	--打开游标
	RAISE NOTICE '打开游标...';
	open cur_tablename;
	
	LOOP
		--从游标中获取数据
		FETCH cur_tablename INTO tablename;
		EXIT WHEN NOT FOUND;
		
		--触发器名称
		triggername := 'tr_'||tablename.table_name;
		RAISE NOTICE '表名: %, 触发器: %',tablename.table_name,triggername;
		
		--删除触发器(一个触发器可以绑定多个表,所以必须同时满足触发器和表两个条件)
		IF EXISTS (select * from pg_class a inner join pg_trigger b on a.oid = b.tgrelid where a.relname = tablename.table_name and b.tgname = triggername) THEN
			RAISE NOTICE '删除触发器 % 在表 %',triggername,tablename.table_name;
			execute 'DROP TRIGGER '||triggername||' ON '||tablename.table_name;
		ELSE
			RAISE NOTICE '触发器 % 在表 % 不存在!!!',triggername,tablename.table_name;
		END IF;
		
		--创建触发器（按语句更新）
		execute 'CREATE TRIGGER '||triggername||' after insert or update or delete on '||tablename.table_name||' for each statement
		execute procedure data_update_trigger_fun()';
		RAISE NOTICE '触发器 % 在表 % 创建成功!!!',triggername,tablename.table_name;
	END LOOP;
	
	close cur_tablename;
	return 1;
end;
$$
LANGUAGE plpgsql;



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

调用

--执行存储过程，批量创建触发器
select batch_create_trigger_fun();
1
2