触发器

PostgreSQL 编程
152
2023/08/02 11:09:39
postgresql

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

简介

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

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

PosgreSQL 有主要有两种触发器：

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

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

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

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

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

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

创建触发器

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

触发器函数类似于常规的自定义函数，比较特殊的一点是，触发器函数不接受任何参数，并具有 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：语句级触发器
  • 触发器函数名：指定触发器函数的名称

示例数据：

-- 雇员表
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 $$;
  • 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：禁用/启用全部触发器