PostgreSql 触发器

脑子进水养啥鱼？

已于 2023-07-11 13:58:08 修改

阅读量6k

点赞数 4

分类专栏： PostgreSql 学习笔记文章标签： postgresql 数据库

于 2023-07-11 13:54:37 首次发布

本文链接：https://blog.csdn.net/songyundong1993/article/details/131658533

版权

PostgreSql 学习笔记专栏收录该内容

63 篇文章 23 订阅

订阅专栏

一、概述

触发器（Trigger）是由事件自动触发执行的一种特殊的存储过程，触发事件可以是对一个表进行INSERT、UPDATE、DELETE等操作。触发器经常用于加强数据的完整性约束和业务规则上的约束等。

二、创建触发器

2.1 语法

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

这里的event可以是下列之一：

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

参数说明：

name：触发器的名称。这必须与同一个表上的任何其他触发器相区别。名称不能是模式限定的，该触发器会继承它所在表的模式。
BEFORE|AFTER|INSTEAD OF：决定该函数是要在事件之前、之后被调用还是会取代该事件。
event：INSERT、UPDATE、DELETE 或者 TRUNCATE 之一，这指定了将要引发该触发器的事件。多个事件可以用 OR 指定，要求传递关系的时候除外。

对于 UPDATE 事件，可以使用下面的语法指定一个列的列表：

 UPDATE OF column_name1 [, column_name2 ... ]

table_name：要使用该触发器的表、视图或外部表的名称（可能是模式限定的）。
referenced_table_name：约束引用的另一个表的名称（可能是模式限定的）。这个选项被用于外键约束并且不推荐用于一般的目的。这只能为约束触发器指定。
NOT DEFERRABLE|[ DEFERRABLE ][ INITIALLY IMMEDIATE|INITIALLY DEFERRED]：该触发器的默认时机。这只能为约束触发器指定。
REFERENCING：这个关键词紧接在一个或者两个关系名的声明之前，这些关系提供对触发语句的传递关系的访问。
OLD TABLE|NEW TABLE：这个子句指示接下来的关系名是用于前映像传递关系还是后映像传递关系。
transition_relation_name：在该触发器中这个传递关系要使用的（未限定）名称。
FOR EACH ROW|FOR EACH STATEMENT：这指定该触发器函数是应该为该触发器事件影响的每一行被引发一次，还是只为每个 SQL 语句被引发一次。如果都没有被指定，FOR EACH STATEMENT 会是默认值。约束触发器只能被指定为 FOR EACH ROW。
condition：一个决定该触发器函数是否将被实际执行的布尔表达式。如果指定了 WHEN，只有condition 返回 true 时才会调用该函数。在 FOR EACH ROW 触发器中，WHEN 条件可以分别写 OLD.column_name 或者 NEW.column_name 来引用列的新旧行值。当然，INSERT 触发器不能引用 OLD 并且 DELETE 触发器不能引用 NEW。 INSTEAD OF 触发器不支持 WHEN 条件。当前，WHEN 表达式不能包含子查询。
FUNCTION_NAME：一个用户提供的函数，它被声明为不用参数并且返回类型 trigger，当触发器引发时会执行该函数。在 CREATE TRIGGER 的语法中，关键词 FUNCTION 和 PROCEDURE 是等效的，但是任何情况下被引用的函数必须是一个函数而不是过程。这里，关键词 PROCEDURE 的使用是有历史原因的并且已经被废弃。
arguments：一个可选的逗号分隔的参数列表，它在该触发器被执行时会被提供给该函数。参数是字符串常量。简单的名称和数字常量也可以被写在这里，但是它们将全部被转换成字符串。请检查该触发器函数的实现语言的描述来找出在函数内部如何访问这些参数，这可能与普通函数参数不同。

2.2 示例

需先为触发器创建一个执行函数，此函数返回的类型为触发器类型。然后创建一个触发器。如有一张学生表（student表）和一张学生的考试成绩表（score表），想要在删除学生记录的同时，删除该学生的成绩，可使用触发器实现。

--创建学生表
CREATE TABLE student(
student_no int primary key,
student_name varchar(40),
age int
);

--创建成绩表
CREATE TABLE score (
student_no int,
chinese_score int,
math_score int,
test_date date
);

--创建触发器执行函数
CREATE OR REPLACE FUNCTION student_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
DELETE FROM score WHERE student_no = OLD.student_no;
RETURN OLD;
END;
$$
LANGUAGE plpgsql;

--创建触发器
CREATE TRIGGER delete_student_trigger
AFTER DELETE ON student
FOR EACH ROW EXECUTE PROCEDURE student_delete_trigger
();

--插入测试数据
INSERT INTO student VALUES(1, '张三', 14);
INSERT INTO student VALUES(2, '李四', 13);
INSERT INTO student VALUES(3, '王二', 15);
INSERT INTO score VALUES(1, 85, 75, date '2013-05-23');
INSERT INTO score VALUES(1, 80, 73, date '2013-09-18');
INSERT INTO score VALUES(2, 68, 83, date '2013-05-23');
INSERT INTO score VALUES(2, 73, 85, date '2013-09-18');
INSERT INTO score VALUES(3, 72, 79, date '2013-05-23');
INSERT INTO score VALUES(3, 78, 82, date '2013-05-23');

--删除数据测试验证触发器是否生效
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | 张三         |  14
          2 | 李四         |  13
          3 | 王二         |  15
(3 rows)

postgres=# select * from score;
 student_no | chinese_score | math_score | test_date
------------+---------------+------------+------------
          1 |            85 |         75 | 2013-05-23
          1 |            80 |         73 | 2013-09-18
          2 |            68 |         83 | 2013-05-23
          2 |            73 |         85 | 2013-09-18
          3 |            72 |         79 | 2013-05-23
          3 |            78 |         82 | 2013-05-23
(6 rows)

postgres=# DELETE FROM student where student_no = 3;
DELETE 1
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | 张三         |  14
          2 | 李四         |  13
(2 rows)

postgres=# select * from score;
 student_no | chinese_score | math_score | test_date
------------+---------------+------------+------------
          1 |            85 |         75 | 2013-05-23
          1 |            80 |         73 | 2013-09-18
          2 |            68 |         83 | 2013-05-23
          2 |            73 |         85 | 2013-09-18
(4 rows)

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

一个被标记为 FOR EACH ROW 的触发器被称为行级触发器，会对该操作修改的每一行都调用一次。例如，一个影响 10 行的 DELETE 将导致在目标关系上的任何 ON DELETE 触发器被独立调用 10 次。与此相反，一个被标记为 FOR EACH STATEMENT 的触发器被称为语句级触发器，只会为任何给定的操作执行一次，不管该操作修改多少行（特别地，一个修改零行的操作将仍会导致任何可用的 FOR EACH STATEMENT 触发器被执行）。

3.1 语句级触发器示例

对 student 表的更新情况进行记录。

--创建日志记录表
CREATE TABLE log_student(
update_time timestamp, --操作的时间
db_user varchar(40), --操作的数据库用户名
opr_type varchar(6) --操作类型：insert、delete、update
);

--创建触发器函数（TG_OP 是触发器函数中的特殊变量，代表 DML 操作类型）
CREATE FUNCTION log_student_trigger ()
RETURNS trigger AS
$$
BEGIN
INSERT INTO log_student values(now(), user, TG_OP);
RETURN NULL;
END;
$$
LANGUAGE "plpgsql";

--创建语句级触发器
CREATE TRIGGER log_student_trigger
AFTER INSERT OR DELETE OR UPDATE ON student
FOR STATEMENT EXECUTE PROCEDURE log_student_trigger ();

--插入数据，验证触发器生效情况
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
(0 rows)

postgres=# select * from log_student;
 update_time | db_user | opr_type
-------------+---------+----------
(0 rows)

postgres=# INSERT INTO student VALUES(1, '张三', 14), (2, '李四', 14);
INSERT 0 2
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | 张三         |  14
          2 | 李四         |  14
(2 rows)

postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 09:55:06.990726 | postgres | INSERT
(1 row)

postgres=# UPDATE student SET age = 15;
UPDATE 2
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | 张三         |  15
          2 | 李四         |  15
(2 rows)

postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 09:55:06.990726 | postgres | INSERT
 2023-07-11 09:55:22.535921 | postgres | UPDATE
(2 rows)

postgres=# UPDATE student SET age = 16 WHERE student_no = 3;
UPDATE 0
postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 09:55:06.990726 | postgres | INSERT
 2023-07-11 09:55:22.535921 | postgres | UPDATE
 2023-07-11 09:56:39.129029 | postgres | UPDATE
(3 rows)

上述示例中可看到，student 表插入更新两行数据，log_student 中均记录对应操作的一条数据，执行了更新语句，但没有更新数据，也会触发一条记录日志操作。说明语句触发器是按语句进行触发的，而不管这条语句实际操作了多少行数据。

3.2 行级触发器示例

对 student 表的更新情况进行记录。

--创建日志记录表
CREATE TABLE log_student(
update_time timestamp, --操作的时间
db_user varchar(40), --操作的数据库用户名
opr_type varchar(6) --操作类型：insert、delete、update
);

--创建触发器函数（TG_OP 是触发器函数中的特殊变量，代表 DML 操作类型）
CREATE FUNCTION log_student_trigger ()
RETURNS trigger AS
$$
BEGIN
INSERT INTO log_student values(now(), user, TG_OP);
RETURN NULL;
END;
$$
LANGUAGE "plpgsql";

--创建行级触发器
CREATE TRIGGER log_student_trigger2
AFTER INSERT OR DELETE OR UPDATE ON student
FOR ROW EXECUTE PROCEDURE log_student_trigger ();

--插入数据，验证触发器生效情况
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
(0 rows)

postgres=# select * from log_student;
 update_time | db_user | opr_type
-------------+---------+----------
(0 rows)

postgres=# INSERT INTO student VALUES(1, '张三', 14), (2, '李四', 14);
INSERT 0 2
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | 张三         |  14
          2 | 李四         |  14
(2 rows)

postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:08.608556 | postgres | INSERT
(2 rows)

postgres=# UPDATE student SET age = 15;
UPDATE 2
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | 张三         |  15
          2 | 李四         |  15
(2 rows)

postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:22.382645 | postgres | UPDATE
 2023-07-11 10:00:22.382645 | postgres | UPDATE
(4 rows)

postgres=# UPDATE student SET age = 16 WHERE student_no = 3;
UPDATE 0
postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:22.382645 | postgres | UPDATE
 2023-07-11 10:00:22.382645 | postgres | UPDATE
(4 rows)

上述示例中可看到，student 表插入更新两行数据，log_student 中均记录对应条数的数据，更新操作没有更新实际行时，不触发记录日志。说明行级触发器是按影响行数进行触发的。

四、BEFORE 触发器与 AFTER 触发器

通常来说，语句级别的 BEFORE 触发器是在语句开始做任何事情之前就被触发了的，而语句级别的 AFTER 触发器是在语句结束时才触发的。行级别的 BEFORE 触发器是在对特定行进行操作之前触发的，而行级别的 AFTER 触发器是在语句结束时才触发的，但是它会在任何语句级别的 AFTER 触发器之前触发。

4.1 BEFORE 触发器示例

BEFORE 触发器可以直接修改 NEW 值以改变实际的更新值，具体示例如下：

--创建触发器函数
CREATE FUNCTION student_use_new_name_tirgger ()
RETURNS trigger AS '
BEGIN
NEW.student_name = NEW.student_name||NEW.student_no;
RETURN NEW;
END;'
LANGUAGE "plpgsql";

--创建触发器
CREATE TRIGGER user_new_name_student_trigger
BEFORE INSERT OR UPDATE ON student
FOR EACH ROW EXECUTE PROCEDURE
student_use_new_name_tirgger ();

--插入数据验证触发器生效情况
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
(0 rows)

postgres=# INSERT INTO student values(3,'王二', 15);
INSERT 0 1
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          3 | 王二3        |  15
(1 row)

4.2 AFTER 触发器示例

如果使用 AFTER 触发器，则修改NEW是没有意义的，示例如下：

postgres=# DROP TRIGGER user_new_name_student_trigger ON student;
DROP TRIGGER
postgres=# delete from student;
DELETE 1
postgres=# CREATE TRIGGER user_new_name_student_trigger
postgres-# AFTER INSERT OR UPDATE ON student
postgres-# FOR EACH ROW EXECUTE PROCEDURE
postgres-# student_use_new_name_tirgger ();
CREATE TRIGGER
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
(0 rows)

postgres=# INSERT INTO student values(3,'王二', 15);
INSERT 0 1
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          3 | 王二         |  15
(1 row)

五、删除触发器

5.1 语法

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

参数说明：

IF EXISTS：如果该触发器不存在则不要抛出一个错误，而是发出一个提示。
name：要移除的触发器的名称。
table_name：定义了该触发器的表的名称（可以是模式限定的）。
CASCADE：自动删除依赖于该触发器的对象，然后删除所有依赖于那些对象的对象（见第5.14节）。
RESTRICT：如果有任何对象依赖于该触发器，则拒绝删除它。这是默认值。

5.2 示例

DROP TRIGGER user_new_name_student_trigger ON student;

删除触发器时，触发器的函数不会被删除。不过，当删除表时，表上的触发器也会被删除。

六、触发器行为

6.1 触发器使用场景

何时	事件	行级	语句级
BEFORE	INSERT/UPDATE/DELETE	表和外部表	表、视图和外部表
BEFORE	TRUNCATE	NULL	表
AFTER	INSERT/UPDATE/DELETE	表和外部表	表、视图和外部表
AFTER	TRUNCATE	NULL	表
INSTEAD OF	INSERT/UPDATE/DELETE	视图	NULL
INSTEAD OF	TRUNCATE	NULL	NULL

INSTEAD OF 触发器，仅用于行级触发器对视图插入、更新或删除的情况。
BEFORE 和 AFTER 触发器作用于视图时，仅用于语句级触发器对视图插入、更新或删除的情况。
BEFORE 和 AFTER 触发器，TRUNCATE 触发时，仅用于语句级触发器对表清空操作的情况。

6.2 触发器函数返回值

语句级触发器应该总是返回 NULL，即必须显式地在触发器函数中写上"ETURN NULL"，如果没有写将导致报错。
BEFORE 和 INSTEAD OF 行级触发器，返回 NULL，则表示忽略对当前行的操作。返回非 NULL 的行，对于 INSERT 和 UPDATE 操作来说，返回的行将成为被插入的行或者是将要更新的行。
AFTER 行级触发器，其返回值会被忽略。
同一事件上有多个触发器，则将按触发器名称的顺序来触发。BEFORE 和 INSTEAD OF 行级触发器，每个触发器返回的行（可能已经被修改）将成为下一个触发器的输入。如果 BEFORE 和 INSTEAD OF 行级触发器返回的内容为空，那么该行上的其他行级触发器也不会被触发。

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

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

NEW：该变量为 INSERT/UPDATE 操作触发的行级触发器中存储新的数据行，数据类型是"RECORD"。在语句级别的触发器中此变量未分配，DELETE 操作触发的行级触发器中此变量也未分配。
OLD：该变量为 UPDATE/DELETE 操作触发的行级触发器中存储原有的数据行，数据类型是"RECORD"。在语句级别的触发器中此变量未分配，INSERT 操作触发的行级触发器中此变量也未分配。
TG_NAME：数据类型是 name 类型，该变量包含实际触发的触发器名。
TG_WHEN：内容为"BEFORE"或"AFTER"字符串用于指定是 BEFORE 触发器还是 AFTER 触发器。
TG_LEVEL：内容为"ROW"或"STATEMENT"字符串用于指定是语句级触发器还是行级触发器。TG_OP：内容为"INSERT"“UPDATE”“DELETE”"TRUNCATE"之一的字符串，用于指定 DML 语句的类型。
TG_RELID：触发器所在表的 OID。
TG_RELNAME：触发器所在表的名称，该变量即将废弃，建议使用 TG_TABLE_NAME 变量来替换此变量。
TG_TABLE_NAME：触发器所在表的名称。
TG_TABLE_SCHEMA：触发器所在表的模式。
TG_NARGS：CREATE TRIGGER 语句中赋予触发器过程参数的个数。
TG_ARGV[]：为text类型的数组；是 CREATE TRIGGER 语句中的参数。

八、事件触发器

8.1 事件触发器概述

PostgreSQL从9.3 版开始支持一种称为"Event Trigger"的触发器，这种触发器主要用于弥补PostgreSQL 以前版本不支持 DDL 触发器的不足。由于事件触发器涉及的权限较大，比如能禁止 DDL 操作等，所以只有超级用户才能创建和修改事件触发器。目前，事件触发器支持以下 3 种 DDL 事件。

ddl_command_start：DDL开始执行前被触发。
ddl_command_end：一个DDL执行完成后被触发。
sql_drop：删除数据库对象前被触发。

8.2 创建事件触发器

1）语法

CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()

参数说明：

name：触发器的名称。在该数据库中这个名称必须唯一。
event：会触发对给定函数调用的事件名称。
filter_variable：用来过滤事件的变量名称。这可以用来限制触发器只为它支持的那一部分情况引发。当前唯一支持的 filter_variable 是 TAG。
filter_value：与该触发器要为其引发的 filter_variable 相关联的一个值列表。对于 TAG，这表示一个命令标签列表（例如 ‘DROP FUNCTION’）。
function_name：一个用户提供的函数，它被声明为没有参数并且返回类型 event_trigger。在CREATE EVENT TRIGGER 的语法中，关键字 CREATE EVENT TRIGGER 和PROCEDURE 是等效的，但是被引用的函数在任何情况下都必须是函数，而不是过程。此处关键字 PROCEDURE的使用是历史性的，已弃用。

2）示例

在创建事件触发器之前，必须先创建触发器函数，事件触发器函数的返回类型为 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 FUNCTION abort_any_command();

8.3 修改事件触发器

1）语法

ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER EVENT TRIGGER name RENAME TO new_name

参数说明：

name：要修改的现有触发器的名称。
new_owner：该事件触发器的新拥有者的用户名。
new_name：该事件触发器的新名称。
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER：这些形式配置事件触发器的触发。一个被禁用的触发器对系统来说仍然是可知的，但是当期触发事件发生时却不会执行它。

2）示例

--禁用事件触发器
ALTER EVENT TRIGGER abort_ddl DISABLE;

8.4 删除事件触发器

1）语法

DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]

参数说明：

IF EXISTS：如果该事件触发器不存在则不要抛出一个错误，而是发出一个提示。
name：要移除的事件触发器的名称。
CASCADE：自动删除依赖于该触发器的对象，然后删除所有依赖于那些对象的对象。
RESTRICT：如果有任何对象依赖于该触发器，则拒绝删除它。这是默认值。

2）示例

--删除事件触发器
DROP EVENT TRIGGER abort_ddl;

脑子进水养啥鱼？

关注

4
点赞
踩
28

收藏

觉得还不错? 一键收藏
打赏
0
评论
PostgreSql 触发器

PostgreSQL从9.3 版开始支持一种称为"Event Trigger"的触发器，这种触发器主要用于弥补PostgreSQL 以前版本不支持 DDL 触发器的不足。由于事件触发器涉及的权限较大，比如能禁止 DDL 操作等，所以只有超级用户才能创建和修改事件触发器。目前，事件触发器支持以下 3 种 DDL 事件。：DDL开始执行前被触发。：一个DDL执行完成后被触发。sql_drop：删除数据库对象前被触发。
复制链接

扫一扫