PostgreSQL 教程: CREATE TRIGGER 创建触发器

摘要：在本教程中，您将学习如何使用 PostgreSQL 的CREATE TRIGGER语句创建触发器。

目录

要在 PostgreSQL 中创建新触发器，请执行以下步骤：

• 首先，使用CREATE FUNCTION语句创建触发函数。
• 其次，通过使用CREATE TRIGGER语句将触发器函数绑定到表上。

如果您不熟悉创建用户定义函数，可以查看 PL/pgSQL 部分。

创建触发器函数语法

触发函数类似于常规的用户定义函数。但是，触发器函数不接受任何参数，并且具有类型为trigger的返回值。

创建触发器函数的语法如下：

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- trigger logic
END;
$$

请注意，您可以使用 PostgreSQL 支持的任何语言创建触发器函数。在本教程中，我们将使用 PL/pgSQL。

触发器函数通过称为 TriggerData 的特殊结构接收有关其调用环境的数据，该结构包含一组局部变量。

例如，OLD和NEW表示触发事件之前或之后表中行的状态。

PostgreSQL 还提供了其他以TG_开头的局部变量，例如TG_WHEN和TG_TABLE_NAME。

定义触发函数后，您可以将其绑定到一个或多个触发事件，例如INSERT、UPDATE 和DELETE。

PostgreSQL CREATE TRIGGER 语句简介

CREATE TRIGGER语句创建一个新的触发器。下面说明了CREATE TRIGGER语句的基本语法：

CREATE TRIGGER trigger_name 
   {BEFORE | AFTER} { event }
   ON table_name
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

在这个语法中：

首先，在TRIGGER关键字后指定触发器的名称。

其次，指定触发触发器的时间。可能是在某个事件之前（BEFORE）或之后（AFTER）发生。

第三，指定调用触发器的事件。该事件可以是INSERT、DELETE、UPDATE或TRUNCATE。

第四，在ON关键字后指定与触发器关联的表的名称。

第五，指定触发器的类型，可以是：

• 由FOR EACH ROW子句指定的行级触发器。
• 由FOR EACH STATEMENT子句指定的语句级触发器。

为每一行触发一个行级触发器，为每个语句触发一个语句级触发器。

假设一个表有 100 行和两个将在DELETE事件发生时触发的触发器。

如果DELETE语句删除 100 行，则行级触发器将触发 100 次，每个删除的行触发一次。另一方面，无论删除了多少行，语句级触发器都会被触发一次。

最后，在EXECUTE PROCEDURE关键字后指定触发函数的名称。

PostgreSQL CREATE TRIGGER 示例

以下语句创建一个名为employees的新表：

DROP TABLE IF EXISTS employees;

CREATE TABLE employees(
   id INT GENERATED ALWAYS AS IDENTITY,
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   PRIMARY KEY(id)
);

假设当员工姓名发生更改时，您希望将更改记录在名为employee_audits的单独表中：

CREATE TABLE employee_audits (
   id INT GENERATED ALWAYS AS IDENTITY,
   employee_id INT NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   changed_on TIMESTAMP(6) NOT NULL
);

首先，创建一个名为log_last_name_changes的新函数：

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
	IF NEW.last_name <> OLD.last_name THEN
		 INSERT INTO employee_audits(employee_id,last_name,changed_on)
		 VALUES(OLD.id,OLD.last_name,now());
	END IF;

	RETURN NEW;
END;
$$

该函数将旧姓氏插入employee_audits表中，包括员工 ID、姓氏以及员工姓氏更改时的更改时间。

OLD表示更新前的行，而NEW表示将更新的新行。OLD.last_name返回更新前的姓氏，NEW.last_name返回新的姓氏。

其次，将触发器函数绑定到employees表上。触发器名称是last_name_changes。在更新last_name列的值之前，会自动调用触发器函数来记录更改。

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

第三步，向employees表中插入一些行：

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');

INSERT INTO employees (first_name, last_name)
VALUES ('Lily', 'Bush');

第四步，查看employees表的内容：

SELECT * FROM employees;

假设Lily Bush将她的姓氏更改为Lily Brown。

第五步，将 Lily 的姓氏更新为新姓氏：

UPDATE employees
SET last_name = 'Brown'
WHERE ID = 2;

第七步，检查Lily的姓氏是否已更新：

SELECT * FROM employees;

从输出中可以看到，Lily 的姓氏已更新。

第八步，验证employee_audits表的内容：

SELECT * FROM employee_audits;

触发器将更改记录在employee_audits表中。

在本教程中，您学习了如何使用 PostgreSQL 的CREATE TRIGGER创建新触发器。