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;

列印頁面