提要:在本教程中,我们将逐步向您展示如何使用 CREATE TRIGGER 语句在 PostgreSQL 中创建第一个触发器。

要创建一个触发器,你需要:

  • 使用 CREATE FUNCTION 语句创建一个触发器函数
  • 使用 CREATE TRIGGER 语句将触发器函数绑定到数据表

如果你还不会创建自定义函数,请参考存储过程部分。

创建触发器函数

触发器函数与普通函数类似,不同之处在于它不包含任何参数,并具有 tigger 类型的返回值,如下所示:

CREATE FUNCTION trigger_function() RETURN trigger AS

提示,你可以使用任何 PostgreSQL 支持的语言来编写触发器函数。在本教程中,我们使用 PL/pgSQL 作为演示。

触发器函数通过一个称为 TriggerData 的特殊结构接收关于其调用环境的数据,其中包含一组局部变量。例如,OLDNEW 表示触发事件之前或之后表中的行的状态。PostgreSQL 提供了其他以 TG_ 作为前缀的局部变量:TG_WHEN, TG_TABLE_NAME 等。

一旦定义了触发器函数,就可以将其绑定到表上的特定操作。

创建触发器

使用 CREATE TRIGGER 语句来创建一个触发器。CREATE TRIGGER 完整的语法很复杂,有很多选项。但是,为了演示,我们将使用 CREATE TRIGGER 语法的简单形式,如下所示:

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

事件可以是:INSERT, UPDATE, DELETETRUNCATE。可以定义触发器在事件前(BEFORE)或事件后(AFTER)触发。INSTEAD OF 仅用于视图的 INSERT, UPDATEDELETE 之上。

正如上文所说,PostgreSQL 提供了2种触发器:行级触发器和语句级触发器。可以通过 FOR EACH ROW(行级)或 FOR EACH STATEMENT(语句级)来指定。

触发器示例

我们将创建一个 employees 表来演示触发器。

CREATE TABLE employees(
   id  serial primary key,
   first_name varchar(40) NOT NULL,
   last_name varchar(40) NOT NULL
);

每当员工的姓氏改变时,我们都会通过触发器将其记录到名为 employee_audits 的表中。

CREATE TABLE employee_audits (
   id serial primary key,
   employee_id int4 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 AS
$BODY$
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;
$BODY$

该函数检查员工的姓氏是否更改,它会将旧姓氏插入 employee_audits 表中,包括员工ID,姓氏和更改时间。

第二步,将 log_last_name_changes 函数绑定到 employees 表。触发器的名字是 last_name_changeslast_name 字段的值更改之前,这个触发器都会自动将本次修改保存到 employee_audits 中。

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');

假设,Lily Bush 结婚了,并且她需要将她的姓氏修改为 Lily Brown,我们可以对它进行修改:

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

此时,你会发现,employee_audits 记录了本次修改。

上一篇:触发器简介
下一篇:管理触发器