postgresql-触发器_postgresql 触发器

触发器概述

PostgreSQL 触发器（trigger）是一种特殊的函数，当某个数据变更事件（INSERT、UPDATE、
DELETE 或者 TRUNCATE 语句）或者数据库事件（DDL 语句）发生时自动执行，而不是由用
户或者应用程序进行调用

基于某个表或者视图数据变更的触发器被称为数据变更触发器（DML 触发器），基于数据
库事件的触发器被称为事件触发器（DDL 触发器）。一般我们更多使用的是数据变更触发器

对于数据变更触发器，PostgreSQL 支持两种级别的触发方式：行级（row-level）触发器和 语句级（statement-level）触发器。这两者的区别在于触发的时机和触发次数。例如，对于一个
影响 20 行数据的 UPDATE 语句，行级触发器将会触发器 20 次，而语句级触发器只会触发 1 次

触发器可以在事件发生之前（BEFORE）或者之后（AFTER）触发。如果在事件之前触发，
它可以跳过针对当前行的修改，甚至修改被更新或插入的数据；如果在事件之后触发，触发器可
以获得所有的变更结果。INSTEAD OF 触发器可以用于替换数据变更的操作，但只能基于视图
定义

下表列出了 PostgreSQL 中支持的各种触发器：

触发器对于多应用共享的数据库而言非常有用，可以将跨应用的功能存储在数据库中，当表
中的数据发生任何变化时都会自动执行触发器的操作。例如，可以用触发器实现数据修改的历史
审计，而不需要各种应用程序实现任何相关的逻辑。

另外，触发器还可以用于实现复杂的数据完整性和业务规则。例如，在非业务时间不允许修
改用户的信息。

但是另一方面，触发器可能带来的问题就是在不清楚它们的存在和逻辑时可能会影响数据修
改的结果和性能。

创建触发器

PostgreSQL 触发器的创建分为两步:

1. 使用 CREATE FUNCTION 语句创建一个触发器函数
2. 使用 CREATE TRIGGER 语句将该函数与表进行关联

-- 语法
CREATE [ OR REPLACE ] FUNCTION trigger_function ()
 RETURNS trigger
AS $$
DECLARE
 declarations
BEGIN
 statements;
 ...
END; $$
LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11

触发器函数与普通函数的区别在于它没有参数，并且返回类型为 trigger；在触发器函数的内部，系统自动创建了许多特殊的变量：

• NEW ，类型为 RECORD，代表了行级触发器 INSERT、UPDATE 操作之后的新数据行。
  对于 DELETE 操作或者语句级触发器而言，该变量为 null；
• OLD，类型为 RECORD，代表了行级触发器 UPDATE、DELETE 操作之前的旧数据行。
  对于 INSERT 操作或者语句级触发器而言，该变量为 null；
• TG_NAME，触发器的名称；
• TG_WHEN，触发的时机，例如 BEFORE、AFTER 或者 INSTEAD OF
• TG_LEVEL，触发器的级别，ROW 或者 STATEMENT；
• TG_OP，触发的操作，INSERT、UPDATE、DELETE 或者 TRUNCATE；
• TG_RELID，触发器所在表的 oid；
• TG_TABLE_NAME，触发器所在表的名称；
• TG_TABLE_SCHEMA，触发器所在表的模式；
• TG_NARGS，创建触发器时传递给触发器函数的参数个数；
• TG_ARGV[]，创建触发器时传递给触发器函数的具体参数，下标从 0 开始。非法的下标
  （小于 0 或者大于等于 tg_nargs）将会返回空值。

-- 使用 CREATE TRIGGER 语句创建一个触发器，语法如下：
CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
 ON table_name
 [FOR [EACH] {ROW | STATEMENT}]
 [WHEN ( condition ) ]
 EXECUTE FUNCTION trigger_function;
1
2
3
4
5
6
7

其中，event 可以是 INSERT、UPDATE、DELETE 或者 TRUNCATE，UPDATE 支持特定字
段（UPDATE OF col1, clo2）的更新操作；触发器可以在事件之前（BEFORE）或者之后（AFTER）
触发，INSTEAD OF 只能用于替代视图上的 INSERT、UPDATE 或者 DELETE 操作；FOR EACH
ROW 表示行级触发器，FOR EACH STATEMENT 表示语句级触发器；WHEN 用于指定一个额
外的触发条件，满足条件才会真正支持触发器函数

我们通过触发器来实现记录员工的信息变更历史，首先创建一个历史记录表
employees_history：

create table employees_history (
 id serial primary key,
 employee_id int null,
 first_name varchar(20) null,
 last_name varchar(25) null,
 email varchar(25) null,
 phone_number varchar(20) null,
 hire_date date null,
 job_id varchar(10) null,
 salary numeric(8,2) null,
 commission_pct numeric(2,2) null,
 manager_id int null,
 department_id int null,
 action_type varchar(10) not null,
 change_dt timestamp not null
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 定义一个触发器函数 track_employees_change
-- 该函数根据不同的操作记录了相应的历史信息、操作类型和操作时间。
create or replace function track_emp_change()
returns trigger 
as $$
begin 
	-- tg_op 触发的操作 
	if tg_op = 'INSERT' then
		insert into public.employees_history
		(employee_id, first_name, last_name, email, phone_number, 
		hire_date, job_id, salary, commission_pct, manager_id,
		department_id, action_type, change_dt)
		values(new.employee_id, new.first_name, new.last_name, 
		new.email, new.phone_number, new.hire_date, new.job_id, new.salary,
		new.commission_pct, new.manager_id, new.department_id,'INSERT'
		,current_timestamp);
	elsif tg_op = 'UPDATE' then
		insert into public.employees_history
		(employee_id, first_name, last_name, email, phone_number, 
		hire_date, job_id, salary, commission_pct, manager_id,
		department_id, action_type, change_dt)
		values(old.employee_id, old.first_name, old.last_name, 
		old.email, old.phone_number, old.hire_date, old.job_id, old.salary,
		old.commission_pct, old.manager_id, old.department_id,'UPDATE'
		,current_timestamp);
	elsif tg_op = 'DELETE' then
		insert into public.employees_history
		(employee_id, first_name, last_name, email, phone_number, 
		hire_date, job_id, salary, commission_pct, manager_id,
		department_id, action_type, change_dt)
		values(old.employee_id, old.first_name, old.last_name, 
		old.email, old.phone_number, old.hire_date, old.job_id, old.salary,
		old.commission_pct, old.manager_id, old.department_id, 'DELETE'
		,current_timestamp);
	end if;
	return new;
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

-- 最后创建一个触发器 trg_employees_change，将该函数与 employees 进行关联：
create trigger trg_employees_change
 before insert or UPDATE or DELETE
 on employees
 for each row
 execute function public.track_emp_change();
1
2
3
4
5
6

-- 测试
insert into employees(employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id)
values(208, 'Tony', 'Dong', 'TonyDong', '01066665678', '2020-05-25',
'IT_PROG', 6000, null, 103, 60);
1
2
3
4
5

SELECT * FROM employees_history;
--我们往 employees 中插入一条记录之后，employees_history 记录了这一操作历史；对于
--UPDATE 和 DELETE 操作也是如此
1
2
3

管理触发器

PostgreSQL 提供了 ALTER TRIGGER 语句，用于修改触发器：

-- 修改触发器的名称
ALTER TRIGGER name ON table_name RENAME TO new_name;
1
2

-- PostgreSQL 还支持触发器的禁用和启用：
ALTER TABLE table_name
{ENABLE | DISABLE} TRIGGER {trigger_name | ALL | USER};
1
2
3

默认创建的触发器处于启用状态；我们也可以使用以上语句禁用或者启用某个触发器、某个
表上的所有触发器（ALL）或用户触发器（不包括内部生成的约束触发器，例如用于外键约束或
延迟唯一性约束以及排除约束的触发器）

视图 information_schema.triggers 中存储了关于触发器的信息

select * from information_schema.triggers;
1

删除触发器

-- 被禁用的触发器仍然存在，只是不会被触发；如果想要删除触发器，可以使用 DROP TRIGGER 语句
DROP TRIGGER [IF EXISTS] trigger_name
ON table_name [RESTRICT | CASCADE];
1
2
3

IF EXISTS 可以避免触发器不存在时的错误提示；CASCADE 表示级联删除依赖于该触发器
的对象，RESTRICT 表示如果存在依赖于该触发器的对象返回错误，默认为 RESTRICT。

-- 将 employees 表上的触发器 trg_employees_change 删除
-- 虽然删除了触发器，但是触发器函数 track_employees_change 仍然存在。
drop trigger trg_employees_change on employees;
1
2
3

事件触发器

除了数据变更触发器之外，PostgreSQL 还提供了另一种触发器：事件触发器 。事件触发器
主要用于捕获全局的 DDL 事件，目前支持 ddl_command_start、ddl_command_end、table_rewrite
和 sql_drop，这些事件支持的完整语句可以参考官网

对于事件触发器的函数而言，同样预定义了两个变量：

• TG_EVENT，触发事件；
• TG_TAG，触发语句。
  对于事件触发器，首先也需要创建一个函数，返回类型为 event_trigger

create or replace function abort_any_command()
returns event_trigger
as $$
begin 
-- 判断当前操作用户是否为超级用户（postgres），如果不是则不允许执行任何 DDL语句。
	if (user != 'postgres') then
	 raise exception '禁止%命令',tg_tag;	
	end if;
end $$
language plpgsql;

1
2
3
4
5
6
7
8
9
10
11

创建事件触发器

-- 使用 create event trigger 语句创建事件触发器：
create event trigger abort_ddl on ddl_command_start
 execute function abort_any_command();
1
2
3

修改触发器

-- alter event trigger 语句可以启用/禁用事件触发器或者修改触发器的名称等：
alter event trigger name disable;
alter event trigger name enable;
alter event trigger name rename to new_name;
1
2
3
4

删除事件触发器

--drop event trigger 语句可以用于删除事件触发器：
drop event trigger [ if exists ] name [ cascade | restrict ];
1
2

-- 删除事件触发器 abort_ddl 
drop event trigger abort_ddl;
1
2