PostgreSQL：如何编写一个触发器__就像与大多数数据库一样，在PostgreSQL中，触发器是一种自动响应事件的方法。也许您想在插入表数据的时候运行函数。也许您想要审计数据的删除，或者只是响应一些UPDATE语句。这正是触发器的好处。这篇文章是对PostgreSQL中触发器的一般性介绍，对于那些想要开始编程的人们来说这是一个简单的教程。: 世界上功能最强大的开源数据库...

作者简介

Hans-Jürgen Schönig公司工程师

译者简介

王志斌，从事数据库产品相关工作，主要致力于postgresql数据库高可用解决方案及云端产品化工作。

校对者简介

崔鹏,任职于海能达通信股份有限公司,数据库开发高级工程师,致力于postgresql数据库在专网通信领域、公共安全领域的应用与推广。

就像与大多数数据库一样，在PostgreSQL中，触发器是一种自动响应事件的方法。也许您想在插入表数据的时候运行函数。也许您想要审计数据的删除，或者只是响应一些UPDATE语句。这正是触发器的好处。这篇文章是对PostgreSQL中触发器的一般性介绍，对于那些想要开始编程的人们来说这是一个简单的教程。

创建一个简单的表

写触发器很容易。你首先需要的是一张简单的表。触发器始终与表关联：

test=# CREATE TABLE t_temperature (
  id serial,
  tstamp timestamptz,
  sensor_id int,
  value float4
);
CREATE TABLE
test=# \d t_temperature
                               Table "public.t_temperature"
Column     | Type                     | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+-------------------------------------------
id         | integer                  |           | not null | nextval('t_temperature_id_seq'::regclass)
tstamp     | timestamp with time zone |           |          |
sensor_id  | integer                  |           |          |
value      | real                     |           |          |

这个例子的目的是检查插入的值，如果我们认为数据是错误的，就静默地“纠正”它们。为了简单起见，所有低于零的值都将简单地设置为-1。

创建触发器: 语法和执行顺序

如果你要定义一个触发器，需要做两件事：

l. 定义函数

l. 基本函数创建触发器

在下一节中，将指导您完成该过程。在开始之前，我们先来看看创建触发器：

test=# \h CREATE TRIGGER
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
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 )

where event can be one of:

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

URL: https://www.postgresql.org/docs/13/sql-createtrigger.html

首先，您可以看到触发器可以在之前或之后执行。但是“之前”和“之后”是什么？如果你插入一行，你可以在插入之前或之后调用函数。如果在实际插入之前调用函数，则可以在该行到达表之前对其进行修改。在AFTER触发器的情况下，trigger函数可以看到刚刚插入的行数据已经插入。

下图显示了工作原理：

CENTER_PostgreSQL_Community

基本上，你可以有很多之前和之后的触发器。重要的是，自PostgreSQL 7.3以来，这些触发器的执行顺序一直是确定的。触发器总是按名称顺序执行。换句话说，PostgreSQL将按字母顺序执行所有BEFORE触发器，执行实际操作，然后按字母顺序执行所有AFTER触发器。

执行顺序非常重要，因为它确保您的代码以确定的顺序运行。为了了解结果，让我们看一个实际的例子。

定义函数和触发器

如前所述，我们希望在值为负的情况下更改插入的值。为此，我编写了一个易于理解的函数：

CREATE OR REPLACE FUNCTION f_temp ()
RETURNS trigger AS
$$
     DECLARE

     BEGIN
          RAISE NOTICE 'NEW: %', NEW;
          IF NEW.value < 0
          THEN
               NEW.value := -1;
               RETURN NEW;
          END IF;
          RETURN NEW;
     END;
$$ LANGUAGE 'plpgsql';

我们看到的是这个新变量。它包含已触发,触发器的当前行。我们可以很容易地访问和修改这个变量，这反过来又会修改表中的值。

注意：如果函数返回NEW，将按预期插入行。但是，如果返回NULL，则该操作将被静默忽略。如果是BEFORE触发器，则不会插入行。

下一步是创建触发器并告诉它调用此函数：

CREATE TRIGGER xtrig
     BEFORE INSERT ON t_temperature
     FOR EACH ROW EXECUTE PROCEDURE f_temp();

我们的触发器只会在插入时触发（在它发生之前不久）。这里还有一点值得注意：在PostgreSQL中，表上的触发器可以为每一行或每一条语句触发。在大多数情况下，人们使用行级触发器，并为每一个修改的行执行一个函数。

测试你的第一个触发器

一旦代码被部署，我们就可以测试它了：

test=# INSERT INTO t_temperature (tstamp, sensor_id, value)
  VALUES  ('2021-05-04 13:23', 1, -196.4), 
          ('2021-06-03 12:32', 1, 54.5)
 RETURNING *;
NOTICE: NEW: (4,"2021-05-04 13:23:00+02",1,-196.4)
NOTICE: NEW: (5,"2021-06-03 12:32:00+02",1,54.5)
id  | tstamp                 | sensor_id | value
----+------------------------+-----------+-------
4   | 2021-05-04 13:23:00+02 | 1         | -1
5   | 2021-06-03 12:32:00+02 | 1         | 54.5
(2 rows)

INSERT 0 2

在本例中，插入了两行。一行被修改–第二行被视为原样。除此之外，我们的触发器还发出两条日志消息，以便我们可以看到NEW的内容。

在触发器中的NEW vs. OLD

上一个示例主要关注INSERT，因此新变量很容易获得。但是，如果您想编写一个触发器来处理UPDATE和DELETE，情况就完全不同了。根据操作的不同，可以使用不同的变量：

l. INSERT: NEW

l. UPDATE: NEW 和OLD

l. DELETE: OLD

l. TRUNCATE: none

换句话说：如果你想为UPDATE写一个触发器，你可以完全访问旧的和新行。如果是DELETE，您可以看到将要删除的行。

到目前为止，我们看到了New和OLD，但还有更多。

在PostgreSQL的触发器函数中预定义变量

PostgreSQL提供了多种可以在触发器函数中访问的预定义变量。基本上函数知道何时调用它，调用它的什么类型的操作等等。

让我们看一下以下代码片段：

CREATE OR REPLACE FUNCTION f_predefined ()
RETURNS trigger AS
$$
     DECLARE

     BEGIN
          RAISE NOTICE 'NEW: %', NEW;
          RAISE NOTICE 'TG_RELID: %', TG_RELID;
          RAISE NOTICE 'TG_TABLE_SCHEMA: %', TG_TABLE_SCHEMA;
          RAISE NOTICE 'TG_TABLE_NAME: %', TG_TABLE_NAME;
          RAISE NOTICE 'TG_RELNAME: %', TG_RELNAME;
          RAISE NOTICE 'TG_OP: %', TG_OP;
          RAISE NOTICE 'TG_WHEN: %', TG_WHEN;
          RAISE NOTICE 'TG_LEVEL: %', TG_LEVEL;
          RAISE NOTICE 'TG_NARGS: %', TG_NARGS;
          RAISE NOTICE 'TG_ARGV: %', TG_ARGV;
          RAISE NOTICE ' TG_ARGV[0]: %', TG_ARGV[0];

          RETURN NEW;
     END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER trig_predefined
     BEFORE INSERT ON t_temperature
     FOR EACH ROW EXECUTE PROCEDURE f_predefined('hans');

INSERT INTO t_temperature (tstamp, sensor_id, value)
     VALUES ('2025-02-12 12:21', 2, 534.4);

如您所见，有很多TG_* 变量，让我们看看它们，看看它们包含了什么：

l.TG_RELID: 我们要触发的表的内部对象id

l.TG_RELNAME + TG_TABLE_SCHEMA + TG_TABLE_NAME: 模式和表名

l. TG_OP: 触发器触发的操作类型INSERT, UPDATE, DELETE 或TRUNCATE?

l. TG_WHEN: 触发器是在操作之前还是之后触发的？

l. TG_LEVEL: 这是语句级还是行级触发器？

l. TG_ARGS: 传递给触发器的函数。

l.TG_ARGV: 参数列表（索引从0开始）

让我们运行上一个清单中显示的代码，看看会发生什么：

NOTICE: NEW: (8,"2025-02-12 12:21:00+01",2,534.4)
NOTICE: TG_RELID: 98399
NOTICE: TG_TABLE_SCHEMA: public
NOTICE: TG_TABLE_NAME: t_temperature
NOTICE: TG_RELNAME: t_temperature
NOTICE: TG_OP: INSERT
NOTICE: TG_WHEN: BEFORE
NOTICE: TG_LEVEL: ROW
NOTICE: TG_NARGS: 1
NOTICE: TG_ARGV: [0:0]={hans}
NOTICE: TG_ARGV[0]: hans
NOTICE: NEW: (8,"2025-02-12 12:21:00+01",2,534.4)

我们在这里看到的是，触发器向我们显示了到底发生了什么。如果您想使函数更通用，这一点很重要。您可以简单地使用同一个函数并将其应用于多个表。

原文链接：https://www.cybertec-postgresql.com/en/postgresql-how-to-write-a-trigger/

PostgreSQL中文社区欢迎广大技术人员投稿

投稿邮箱：press@postgres.cn

### **作者简介**

#### Hans-Jürgen Schönig公司工程师
### **译者简介**

#### 王志斌，从事数据库产品相关工作，主要致力于postgresql数据库高可用解决方案及云端产品化工作。
### **校对者简介**

#### 崔鹏,任职于海能达通信股份有限公司,数据库开发高级工程师,致力于postgresql数据库在专网通信领域、公共安全领域的应用与推广。

#### 就像与大多数数据库一样，在PostgreSQL中，触发器是一种自动响应事件的方法。也许您想在插入表数据的时候运行函数。也许您想要审计数据的删除，或者只是响应一些UPDATE语句。这正是触发器的好处。这篇文章是对PostgreSQL中触发器的一般性介绍，对于那些想要开始编程的人们来说这是一个简单的教程。

### **创建一个简单的表**
#### 写触发器很容易。你首先需要的是一张简单的表。触发器始终与表关联：
```
test=# CREATE TABLE t_temperature (
  id serial,
  tstamp timestamptz,
  sensor_id int,
  value float4
);
CREATE TABLE
test=# \d t_temperature
                               Table "public.t_temperature"
Column     | Type                     | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+-------------------------------------------
id         | integer                  |           | not null | nextval('t_temperature_id_seq'::regclass)
tstamp     | timestamp with time zone |           |          |
sensor_id  | integer                  |           |          |
value      | real                     |           |          |
```
#### 这个例子的目的是检查插入的值，如果我们认为数据是错误的，就静默地“纠正”它们。为了简单起见，所有低于零的值都将简单地设置为-1。

### **创建触发器: 语法和执行顺序**
#### 如果你要定义一个触发器，需要做两件事：
#### l. 定义函数
#### l. 基本函数创建触发器
#### 在下一节中，将指导您完成该过程。在开始之前，我们先来看看创建触发器：
```
test=# \h CREATE TRIGGER
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
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 )
 
where event can be one of:
 
     INSERT
     UPDATE [ OF column_name [, ... ] ]
     DELETE
     TRUNCATE
```
#### URL: https://www.postgresql.org/docs/13/sql-createtrigger.html

#### 首先，您可以看到触发器可以在之前或之后执行。但是“之前”和“之后”是什么？如果你插入一行，你可以在插入之前或之后调用函数。如果在实际插入之前调用函数，则可以在该行到达表之前对其进行修改。在AFTER触发器的情况下，trigger函数可以看到刚刚插入的行数据已经插入。
#### 下图显示了工作原理：

![CENTER_PostgreSQL_Community]( /images/news/2021/20210409_2212_图片1-2021.4_.9_.png)

#### 基本上，你可以有很多之前和之后的触发器。重要的是，自PostgreSQL 7.3以来，这些触发器的执行顺序一直是确定的。触发器总是按名称顺序执行。换句话说，PostgreSQL将按字母顺序执行所有BEFORE触发器，执行实际操作，然后按字母顺序执行所有AFTER触发器。
#### 执行顺序非常重要，因为它确保您的代码以确定的顺序运行。为了了解结果，让我们看一个实际的例子。

### **定义函数和触发器**
#### 如前所述，我们希望在值为负的情况下更改插入的值。为此，我编写了一个易于理解的函数：
```
CREATE OR REPLACE FUNCTION f_temp ()
RETURNS trigger AS
$$
     DECLARE
 
     BEGIN
          RAISE NOTICE 'NEW: %', NEW;
          IF NEW.value < 0
          THEN
               NEW.value := -1;
               RETURN NEW;
          END IF;
          RETURN NEW;
     END;
$$ LANGUAGE 'plpgsql';
```
#### 我们看到的是这个新变量。它包含已触发,触发器的当前行。我们可以很容易地访问和修改这个变量，这反过来又会修改表中的值。
#### 注意：如果函数返回NEW，将按预期插入行。但是，如果返回NULL，则该操作将被静默忽略。如果是BEFORE触发器，则不会插入行。
#### 下一步是创建触发器并告诉它调用此函数：
```
CREATE TRIGGER xtrig
     BEFORE INSERT ON t_temperature
     FOR EACH ROW EXECUTE PROCEDURE f_temp();
```
#### 我们的触发器只会在插入时触发（在它发生之前不久）。这里还有一点值得注意：在PostgreSQL中，表上的触发器可以为每一行或每一条语句触发。在大多数情况下，人们使用行级触发器，并为每一个修改的行执行一个函数。

### **测试你的第一个触发器**
#### 一旦代码被部署，我们就可以测试它了：
```
test=# INSERT INTO t_temperature (tstamp, sensor_id, value)
  VALUES  ('2021-05-04 13:23', 1, -196.4), 
          ('2021-06-03 12:32', 1, 54.5)
 RETURNING *;
NOTICE: NEW: (4,"2021-05-04 13:23:00+02",1,-196.4)
NOTICE: NEW: (5,"2021-06-03 12:32:00+02",1,54.5)
id  | tstamp                 | sensor_id | value
----+------------------------+-----------+-------
4   | 2021-05-04 13:23:00+02 | 1         | -1
5   | 2021-06-03 12:32:00+02 | 1         | 54.5
(2 rows)
 
INSERT 0 2
```
#### 在本例中，插入了两行。一行被修改–第二行被视为原样。除此之外，我们的触发器还发出两条日志消息，以便我们可以看到NEW的内容。

### **在触发器中的NEW vs. OLD**
#### 上一个示例主要关注INSERT，因此新变量很容易获得。但是，如果您想编写一个触发器来处理UPDATE和DELETE，情况就完全不同了。根据操作的不同，可以使用不同的变量：
#### l. INSERT: NEW
#### l. UPDATE: NEW 和OLD
#### l. DELETE: OLD
#### l. TRUNCATE: none
#### 换句话说：如果你想为UPDATE写一个触发器，你可以完全访问旧的和新行。如果是DELETE，您可以看到将要删除的行。
#### 到目前为止，我们看到了New和OLD，但还有更多。

### **在PostgreSQL的触发器函数中预定义变量**
#### PostgreSQL提供了多种可以在触发器函数中访问的预定义变量。基本上函数知道何时调用它，调用它的什么类型的操作等等。
#### 让我们看一下以下代码片段：
```
CREATE OR REPLACE FUNCTION f_predefined ()
RETURNS trigger AS
$$
     DECLARE
 
     BEGIN
          RAISE NOTICE 'NEW: %', NEW;
          RAISE NOTICE 'TG_RELID: %', TG_RELID;
          RAISE NOTICE 'TG_TABLE_SCHEMA: %', TG_TABLE_SCHEMA;
          RAISE NOTICE 'TG_TABLE_NAME: %', TG_TABLE_NAME;
          RAISE NOTICE 'TG_RELNAME: %', TG_RELNAME;
          RAISE NOTICE 'TG_OP: %', TG_OP;
          RAISE NOTICE 'TG_WHEN: %', TG_WHEN;
          RAISE NOTICE 'TG_LEVEL: %', TG_LEVEL;
          RAISE NOTICE 'TG_NARGS: %', TG_NARGS;
          RAISE NOTICE 'TG_ARGV: %', TG_ARGV;
          RAISE NOTICE ' TG_ARGV[0]: %', TG_ARGV[0];
 
          RETURN NEW;
     END;
$$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER trig_predefined
     BEFORE INSERT ON t_temperature
     FOR EACH ROW EXECUTE PROCEDURE f_predefined('hans');
 
INSERT INTO t_temperature (tstamp, sensor_id, value)
     VALUES ('2025-02-12 12:21', 2, 534.4);
```
#### 如您所见，有很多TG_* 变量，让我们看看它们，看看它们包含了什么： 
#### l.TG_RELID: 我们要触发的表的内部对象id
#### l.TG_RELNAME + TG_TABLE_SCHEMA + TG_TABLE_NAME: 模式和表名
#### l. TG_OP: 触发器触发的操作类型INSERT, UPDATE, DELETE 或TRUNCATE?
#### l. TG_WHEN: 触发器是在操作之前还是之后触发的？
#### l. TG_LEVEL: 这是语句级还是行级触发器？
#### l. TG_ARGS: 传递给触发器的函数。
#### l.TG_ARGV: 参数列表（索引从0开始）
#### 让我们运行上一个清单中显示的代码，看看会发生什么：
```
NOTICE: NEW: (8,"2025-02-12 12:21:00+01",2,534.4)
NOTICE: TG_RELID: 98399
NOTICE: TG_TABLE_SCHEMA: public
NOTICE: TG_TABLE_NAME: t_temperature
NOTICE: TG_RELNAME: t_temperature
NOTICE: TG_OP: INSERT
NOTICE: TG_WHEN: BEFORE
NOTICE: TG_LEVEL: ROW
NOTICE: TG_NARGS: 1
NOTICE: TG_ARGV: [0:0]={hans}
NOTICE: TG_ARGV[0]: hans
NOTICE: NEW: (8,"2025-02-12 12:21:00+01",2,534.4)
```
#### 我们在这里看到的是，触发器向我们显示了到底发生了什么。如果您想使函数更通用，这一点很重要。您可以简单地使用同一个函数并将其应用于多个表。

#### 原文链接：https://www.cybertec-postgresql.com/en/postgresql-how-to-write-a-trigger/

#### **PostgreSQL中文社区欢迎广大技术人员投稿**
#### **投稿邮箱：press@postgres.cn**