postgreSQL触发器

一、概述

1、什么是触发器

触发器的功能就是为了解决这类问题而设计的，当你更新或查询某个资料表时会触动触发器，触发器就会照您所设计的流程，同步去插入、更新、删除其他资料，你不再需要重复下达多次的SQL命令就能达成一连串资料的同步处理。

触发器是某个数据库操作发生时被自动调用的函数。可以在INSERT、UPDATE或DELETE操作之前或之后调用触发器。PostgreSQL支持两种类型的触发器，一种是数据行级触发器，另外一种是语句级触发器。对于数据行级的触发器，触发发触发器的语句每操作一个数据行，它就被执行一次。对于语句级的触发器，它只会被执行一次。

创建触发器以前，必须定义触发器使用的函数。这个函数不能有任何参数，它的返回值的类型必须是trigger。函数定义好以后，用命令CREATE TRIGGER创建触发器。多个触发器可以使用同一个函数。

2、语句级触发器与行级触发器

• 语句级的触发器是指执行每个 SQL 时，只执行一次，行级触发器则指每行都会执行一次。

• 一个修改零行的操作会导致合适的语句级触发器被执行，但不会触发行级触发器。

• 批量插入时，语句级别的触发器只触发一次，不管 affected row 是否为 0，但是行级触发器的触发次数为 affected row。

3、触发器的调用时机

触发器按按执行的时间被分为 before触发器 和 after触发器。

• 语句级的 before触发器 在语句开始执行前被调用，
• 语句级的 after触发器 在语句开始执行结束后被调用。
• 数据行级的 before触发器 在操作每个数据行以前被调用，
• 数据行级的 after触发器 在操作每个数据行以后被调用。

4、触发器的返回值

**语句级的触发器应该总是返回NULL。**即必须显式地在触发器函数中写上 RETURN NULL，如果没有写，将导致出错。报错信息如下所示：

行级的 before触发器 的返回值不同，它对触发操作的影响也不同。

• 如果它返回NULL,触发这个触发器的INSERT UPDATE DELETE命令不会被执行。
• 如果返回非空的值，则INSERT UPDATE DELETE 命令继续执行。
• 对于UPDATE和INSERT操作触发的行级before触发器，如果它返回的数据行与更新以后的或被插入的数据行不相同，则以触发器返回的数据行作为新的更新以后的数据行和被插入的数据行。

行级after触发器的返回值总是被忽略，可以返回NULL。

• 如果同一表上，对同一个事件定义了多个触发器，这些触发器将按它们的名字的字母顺序被触发。
• 对于行级before触发器来说，前一个触发器返回的数据行作为后一个触发器的输入。如果任何一个行级before触发器返回NULL，后面的触发器将停止执行，触发触发器的INSERT UPDATE DELETE命令也不会被执行。

5、注意项

行级 before 触发器一般用于 检查 和 修改 将被插入和更新的数据。 行级 after 触发器一般用于将表中被更新的数据记录到其它的表中，或者检查与其它的表中的数据是否是一致的。

before触发器的执行效率比after触发器高，在before触发器和after触发器都能被使用的情况下，应该选择before触发器。

一个触发器在执行的过程中，如果执行了其它的 SQL 命令，可能会触发其它的触发器，这被称作触发器级联。对于触发器级联的层次，系统没有任何限制，但触发器级联可能会调用前面已经执行过的触发器，从而引起死循环，系统不会检测这种现象，定义触发器的用户应该保证这种现象不会发生。

定义触发器的时候，也可以为它指定参数（在CREATE TRIGGER命令中中指定）。系统提供了特殊的接口来访问这些参数。

触发器在被调用时，系统会自动传递一些数据给它，这些数据包括触发触发器的事件类型（例如INSERT或UPDATE），对于行级触发器，还包括 NEW 数据行（对于INSERT和 UPDATE触发器）和OLD数据行（对于UPDATE和DELETE触发器）。每种可以用来书写触发器函数的语言都提供了取这些数据的方法。

语句级别的触发器在执行过程中，无法查看该语句插入、删除或更新的任何数据行。

还有一种特殊的触发器叫约束触发器，这种触发器的执行时间可以被命令 SET CONSTRAINTS 控制，详细信息参考《SQL命令手册》对CREATE CONSTRAINT TRIGGER 命令的解释。

6、数据可见规则

触发器在执行过程中，如果执行 SQL 命令访问触发器的父表中的数据，这些SQL命令遵循下面的数据可见规则，这些规则决定它们能否看见触发触发器的操作修改的表中的数据行：

（1）语句级的before触发器在执行过程中，该语句的所有的对表中的数据的更新对它都不可见。语句级的after触发器在执行过程中，该语句的所有的对表中的数据的更新对它都可见。

（2）行级before触发器在执行过程中，前面所有的已经被同一个命令处理的数据行对它是可见的，但触发该触发器的数据行的更新操作的结果（插入、更新或删除）对它是不可见的。行级after触发器在执行过程中，前面所有的已经被同一个命令处理的数据行对它是可见的。

7、触发器函数中的特殊变量

当把一个 PL/pgSQL 函数当作触发器函数调用的时候，系统会在顶层的声明段里自动创建几个特殊变量，比如在之前例子中的 “NEW”、“OLD”、“TG_OP” 变量等。可以使用的变量有如下这些。

1. NEW：该变量为 INSERT/UPDATE 操作触发的行级触发器中存储的新的数据行，数据类型是 RECORD。在语句级别的触发器里此变量没有分配，DELETE 操作触发的行级触发器中此变量也没有分配。
2. OLD：该变量为 UPDATE/DELETE 操作触发的行级触发器中存储的旧数据行，数据类型是 RECORD。在语句级别的触发器里此变量没有分配， INSERT 操作触发的行级触发器中此变量也没有分配。
3. TG_NAME：数据类型是 name，该变量包含实际触发的触发器名。
4. TG_WHEN: 内容为 BEFORE 或 AFTER 的字符串，用于指定是 BEFORE 触发器还是 AFTER 触发器。
5. TG_LEVEL: 内容为 ROW 或 STATEMENT 的字符串用于指定是语句级触发器还是行级触发器。
6. TG_OP: 内容为 INSERT、UPDATE、DELETE、TRUNCATE 之一的字符串，用于指定 DML 语句的类型。
7. TG_RELID: 触发器所在表的 OID。
8. TG_RELNAME: 数据类型是 name，表示触发器作用的表的名字。它与下面的变量TG_TABLE_NAME的作用是一样的。
9. TG_TABLE_NAME: 触发器所在表的名称。
10. TG_TABLE_SCHEMA: 触发器所在表的模式。
11. TG_NARGS: 在 CREATE TRIGGER 语句里面赋予触发器过程的参数个数。
12. TG_ARGV[]: 为 text 类型的一个数组；是 CREATE TRIGGER 语句里的参数。

二、触发器的操作

1、查看触发器

select * from information_schema.triggers
1

2、创建触发器

（1）创建触发器的语法如下：

CREATE [ CONSTRAINT ] TRIGGER name 
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]}
ON table_name
[ FROM referenced_table_name ]
{ NOT DEFERRABLE | [ DEFEREABLE ] { IINITIALLY IMMEDIATE | INITIALLY DEFERED} }
FOR [ EACH ] { ROW | STATEMENT }
[ WHEN { condition }]
EXECUTE PROCEDURE function_name ( arguments )


CREATE [OR REPLACE] TRIGGER TRIGGER_NAME
{BEFORE | AFTER} TRIGGER_EVENT
ON TABLE_NAME
[FOR EACH ROW]
[WHEN TRIGGER_CONDITION]
TRIGGER_BODY

语法解释：
TRIGGER_NAME  触发器名称
BEFORE | AFTER  指定触发器是在触发事件发生之前触发或者发生之后触发
TRIGGER_EVENT 触发事件,在DML触发器中主要为INSERT、UPDATE、DELETE等
TABLE_NAME  表名,表示发生触发器作用的对象
FOR EACH ROW  指定创建的是行级触发器,若没有该子句则创建的是语句级触发器
WHEN TRIGGER_CONDITION  添加的触发条件
TRIGGER_BODY  触发体,是标准的PL/SQL语句块
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

（2）创建触发器的步骤：

先为触发器建一个执行函数，此函数的返回类型为触发器类型 trigger；然后即可创建相应的触发器。

例如当删除学生表(student)中的一条记录时，把这个学生在成绩表 (score) 中的成绩记录也删除掉，这时就可以使用触发器。

（3）先建触发器的执行函数：

CREATE OR REPLEASE FUNCTION student_delete_trigger_fun()
returns trigger as $$
begin
    delete from score where student_no = old.student_no;
    return old;
end;
$$
language plpgsql;
1
2
3
4
5
6
7
8

（4）再创建这个触发器：

CREATE TRIGGER delete_student_trigger
after delete on student
for each row execute procedure student_delete_trigger_fun();
1
2
3

3、删除触发器

语法如下：

# DROP TRIGGER IF EXISTS 触发器名称 ON 表名称;
DROP TRIGGER [ IF EXISTS ] NAME ON TABLE [ CASCADE | RESTRICT ];
1
2

删除触发器时，触发器的函数不会被删除。不过，当表删除时，表上的触发器也会被删除，你可以使用 DROP FUNCTION fun_name 直接删除触发器函数。

四、案例

1、简单案例

将添加到student表中的数据，同步添加到user表。

-- 触发器函数
CREATE OR REPLACE FUNCTION "public"."student_trigger_fun"() 
      RETURNS trigger  AS $BODY$
 BEGIN
    IF  (TG_OP='INSERT') THEN 
        INSERT INTO user values(NEW.id,NEW.name,NEW.age);
    END IF;
    RETURN NEW;
 END;
  $BODY$
  LANGUAGE 'plpgsql';
 
-- 触发器
CREATE TRIGGER student_trigger AFTER INSERT OR UPDATE ON "public"."student"
FOR EACH ROW
EXECUTE PROCEDURE "public"."student_trigger_fun"();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2、进阶案例

将对student表的增、删、改操作，同步更新到user表。

-- 触发器函数
CREATE OR REPLACE FUNCTION "public"."student_trigger_fun"() 
RETURNS TRIGGER AS $BODY$ BEGIN
	IF( TG_OP = 'INSERT') THEN
		INSERT INTO USER VALUES (NEW.id, NEW.name, NEW.age);
	RETURN NEW;
	ELSIF( TG_OP = 'UPDATE') THEN
		UPDATE USER SET id=NEW.id, name=NEW.name, age=NEW.age WHERE id=OLD.id;
	RETURN NEW;
	ELSIF( TG_OP = 'DELETE') THEN
		DELETE USER WHERE id=OLD.id;
	RETURN OLD;
	END IF;
END;
$BODY$ LANGUAGE'plpgsql';

-- 触发器
CREATE TRIGGER student_trigger AFTER INSERT OR UPDATE ON "public"."student" 
FOR EACH ROW
EXECUTE PROCEDURE "public"."student_trigger_fun"();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

五、事件触发器

创建事件触发器的语法如下：

CREATE EVENT TRIGGER 
ON event
[ WHEN filter_variable IN (filter_value [,...]) [ and ...]]
EXECUTE PROCEDURE function_name()
1
2
3
4

在创建事件触发器之前，必须先创建触发器函数，事件触发器函数的返回类型为 event_trigger，注意，其与普通触发器函数的返回类型 （trigger）是不一样的。

在官方手册中，有一个禁止所有 DDL 语句的例子，如下：

CREATE OR REPLACE FUNCTION abort_any_command()
returns event_trigger
language plpgsql
AS $$
BEGIN
    RAISE EXCEPTION 'command % is disabled', tg_tag;
END;
$$;


CREATE EVENT TRIGGER abort_DDL ON DDL_command_start
EXECUTE PROCEDURE abort_any_command();
1
2
3
4
5
6
7
8
9
10
11
12

现在执行 DDL 语句将会报错

如果想再允许 DDL 操作，可以禁止事件触发器，

ALTER EVENT TRIGGER abort_ddl DISABLE;
1

TG_EVENT: 为 “ddl_command_start”、“ddl_command_end”、“sql_drop” 之一。

TG_TAG: 只具体的哪种 DDL 操作，如 “CREATE TABLE”、“DROP TABLE” 等。

六、参考

https://www.yiibai.com/postgresql/postgresql-trigger.html