触发器

本章将讨论触发器，包括：创建、删除、更新、禁用、启用触发器等知识。

简介

PostgreSQL 触发器是每当与表关联的事件发生时自动调用的函数。事件可以是：INSERT、UPDATE、DELETE 或 TRUNCATE。

触发器是与表关联的特殊的用户自定义函数。要创建新的触发器，首先定义一个触发器函数，然后将该触发器函数绑定到表。

PosgreSQL 有主要有两种触发器：

• 行级触发器
• 语句级触发器

它们的区别在于调用触发器的次数和时间。比如发出修改20行的 UPDATE 语句，行级触发器将被调用20次，而语句级触发器将只调用1次。

可以指定触发器是在事件之前还是之后调用。如果在事件之前调用，它可以跳过当前行的操作，甚至更改正在修改或插入的行。如果在事件之后调用，则所有更改都可供触发器使用

如果数据库被各种应用程序访问，并且希望在数据库中保持跨应用功能，只要表的数据被修改，触发器就会自动运行。例如，希望保留数据历史记录，而不要求应用程序具有检查每个事件（比如 INSERT或UPDATE）的逻辑。

此外，还可以使用触发器来维护复杂的数据完整性规则，这些规则除了数据库级别之外无法在其它地方实现。比如，当 customer 表添加记录的时候，还必须在银行和信用表中创建其它记录。

使用触发器最主要的缺点是，你必须知道触发器存在并了解其逻辑，才能弄清楚数据更改时的影响。

PostgreSQL 扩展了 SQL 标准的触发器：

• 添加了 TRUNCATE 事件的触发器
• 允许在视图上定义语句级触发器
• 要求必须使用自定义函数作为触发器操作，而 SQL 标准允许使用任何 SQL 命令

创建触发器

创建触发器分两个步骤：

• 首先，使用 CREATE FUNCTION 创建一个触发器函数
• 其次，使用 CREATE TRIGGER 将触发器函数绑定到表

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

CREATE FUNCTION 触发器函数名() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- 触发器逻辑
END $$;

CREATE TRIGGER 触发器名称 
   {BEFORE | AFTER} { 事件 }
   ON 表名
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE 触发器函数名

• 触发时间：
  • BEFORE：在事件前触发
  • AFTER：在事件后触发
• 事件：指定调用触发器的事件：INSERT、DELETE、UPDATE 或 TRUNCATE。
• 表名：指定与触发器关联的表的名称
• 触发器类型
  • FOR EACH ROW：行级触发器
  • FOR EACH STATEMENT：语句级触发器
• 触发器函数名：指定触发器函数的名称

• BEFORE：在事件前触发
• AFTER：在事件后触发

示例数据：

-- 雇员表
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
);

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 $$;

• OLD 表示更新前的行，而 NEW 表示更新后的行。

创建 last_name_changes触发器：

• 绑定到 employees
• 监听 UPDATE 事件，并在事件前触发
• 是一个行级触发器
• 触发器函数是 log_last_name_changes()

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

删除触发器

使用 DROP TRIGGER 来删除触发器：

DROP TRIGGER [IF EXISTS] 触发器名称 
ON table_name [ CASCADE | RESTRICT ];

可以使用 ALTER TRIGGER 对触发器进行重命名：

ALTER TRIGGER 触发器名称
ON 表名 
RENAME TO 新的触发器名称;

注意，它能做的只有这个功能。如果要修改触发器的定义，你需要在事务中先删除、再重新创建：

BEGIN;

DROP TRIGGER IF EXISTS 触发器名称;

CREATE TRIGGER 触发器名称
  BEFORE UPDATE
  ON 表名
  FOR EACH ROW
  EXECUTE PROCEDURE 触发器函数();

COMMIT;

禁用/启用触发器

使用 ALTER TABLE DISABLE|ENABLE TRIGGER 来禁用/启用触发器：

ALTER TABLE 表名
DISABLE | ENABLE TRIGGER 触发器名 | ALL

• DISABLE：禁用触发器
• ENABLE：启用触发器
• 触发器名：禁用/启用指定的触发器
• ALL：禁用/启用全部触发器