PostgreSQL - 触发器

---

PostgreSQL触发器是数据库回调函数，当指定的数据库事件发生时自动执行/调用。

以下是有关 PostgreSQL 触发器的要点 -

• 可以指定触发 PostgreSQL 触发器

  • 在尝试对行进行操作之前（在检查约束并尝试 INSERT、UPDATE 或 DELETE 之前）

  • 操作完成后（检查约束并完成 INSERT、UPDATE 或 DELETE 后）

  • 代替操作（在视图上插入、更新或删除的情况下）

• 对于操作修改的每一行，都会调用一次标记为 FOR EACH ROW 的触发器。相反，标记为 FOR EACH STATEMENT 的触发器对于任何给定操作仅执行一次，无论它修改了多少行。

• WHEN 子句和触发器操作都可以使用NEW.column-name和OLD.column-name形式的引用来访问正在插入、删除或更新的行的元素，其中 column-name 是来自与触发器关联的表。

• 如果提供了 WHEN 子句，则仅对 WHEN 子句为 true 的行执行指定的 PostgreSQL 语句。如果未提供 WHEN 子句，则将对所有行执行 PostgreSQL 语句。

• 如果为同一事件定义了多个相同类型的触发器，它们将按名称的字母顺序触发。

• BEFORE、AFTER 或 INSTEAD OF 关键字确定何时执行与插入、修改或删除关联行相关的触发器操作。

• 当与触发器关联的表被删除时，触发器会自动删除。

• 要修改的表必须与触发器附加到的表或视图存在于同一数据库中，并且必须仅使用tablename，而不是database.tablename。

• 指定 CONSTRAINT 选项后会创建约束触发器。这与常规触发器相同，只是触发器触发的时间可以使用 SET CONSTRAINTS 进行调整。当约束触发器实现的约束被违反时，预计约束触发器会引发异常。

句法

创建触发器的基本语法如下 -

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

这里，event_name可以是对上述表table_name的 INSERT、DELETE、 UPDATE和TRUNCATE数据库操作。您可以选择在表名称后指定 FOR EACH ROW。

以下是在表的一个或多个指定列上的 UPDATE 操作上创建触发器的语法，如下所示 -

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
];

例子

让我们考虑这样一种情况，我们希望对插入 COMPANY 表中的每条记录进行审计试验，我们将按如下方式新建该表（如果已有 COMPANY 表，则删除它）。

testdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

为了保持审计试验，我们将创建一个名为 AUDIT 的新表，只要 COMPANY 表中有新记录的条目，就会在其中插入日志消息 -

testdb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

这里，ID是AUDIT记录ID，EMP_ID是ID，它将来自COMPANY表，DATE将保留在COMPANY表中创建记录时的时间戳。现在，让我们在 COMPANY 表上创建一个触发器，如下所示 -

testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

其中auditlogfunc()是一个PostgreSQL过程并具有以下定义 -

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

现在，我们将开始实际工作。让我们开始在 COMPANY 表中插入记录，这将导致在 AUDIT 表中创建审核日志记录。因此，让我们在 COMPANY 表中创建一条记录，如下所示 -

testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

这将在 COMPANY 表中创建一条记录，如下所示 -

 id | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000

同时，AUDIT 表中将创建一条记录。该记录是触发器的结果，该触发器是我们在 COMPANY 表上的 INSERT 操作中创建的。同样，您可以根据您的要求在 UPDATE 和 DELETE 操作上创建触发器。

 emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

上市触发因素

您可以从pg_trigger表中列出当前数据库中的所有触发器，如下所示 -

testdb=# SELECT * FROM pg_trigger;

上面给出的 PostgreSQL 语句将列出所有触发器。

如果要列出特定表上的触发器，请使用带有表名的 AND 子句，如下所示 -

testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

上面给出的 PostgreSQL 语句也将仅列出一项，如下所示 -

     tgname
-----------------
 example_trigger
(1 row)

掉落触发器

以下是 DROP 命令，可用于删除现有触发器 -

testdb=# DROP TRIGGER trigger_name;