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子句，PostgreSQL语句只会对WHEN子句为true的行执行。如果没有提供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 可以是表格中的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....
];

示例

让我们考虑一个情况，在这种情况下，我们希望为插入到公司表中的每条记录保留审计记录，我们将按照以下方式创建新的公司表（如果您已经拥有它，请删除公司表）。

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表的插入操作上创建了这个触发器。类似地，您可以根据需求在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;