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 子句为真的行执行指定的 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 是来自 COMPANY 表的 ID，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;
打印页面
上一页
下一页
广告