Learning PostgresSQL讀書筆記: 第8章 Triggers and Rules

本章將討論以下內容：
? 探索 PostgreSQL 中的規則
? 管理 PostgreSQL 中的觸發器
? 事件觸發器

探索 PostgreSQL 中的規則

文檔中的這段話闡述了rule和trigger的區別：

PostgreSQL 規則系統允許定義在數據庫表中插入、更新或刪除時執行的替代操作。粗略地說，當對給定表執行給定命令時，規則會執行其他命令。或者，INSTEAD 規則可以用另一個命令替換給定命令，或者導致命令根本不執行。規則也用于實現 SQL 視圖。重要的是要認識到規則實際上是一種命令轉換機制或命令宏。轉換發生在命令開始執行之前。如果您確實想要一個針對每個物理行獨立觸發的操作，則可能需要使用觸發器而不是規則。

簡單來說，rule和trigger接收到觸發事件的操作時，可以：

替換為新的操作（INSTEAD）
額外做新的操作（ALSO）
什么都不做（INSTEAD NOTHING）

理解 OLD 和 NEW 變量

這兩個稱為pseudorelations。說明見這里。

NEW 記錄
行級觸發器中 INSERT/UPDATE 操作的新數據庫行。此變量在語句級觸發器和 DELETE 操作中為空。
OLD 記錄
行級觸發器中 UPDATE/DELETE 操作的舊數據庫行。此變量在語句級觸發器和 INSERT 操作中為空。

INSERT/UPDATE/DELETE 規則

使用這里的示例數據。

ALSO 選項

postgres=# \d emp;Table "public.emp"Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------empno    | integer               |           | not null |ename    | character varying(10) |           |          |job      | character varying(9)  |           |          |mgr      | integer               |           |          |hiredate | character varying(10) |           |          |sal      | numeric(7,2)          |           |          |comm     | numeric(7,2)          |           |          |deptno   | integer               |           | not null |create table emp_log(empno integer, action varchar(10));

INSERT/UPDATE/DELETE rule示例：

create or replace rule r_ins_emp
as on INSERT to emp
DO ALSO
insert into emp_log values (NEW.empno, 'INSERT');create or replace rule r_updt_emp
as on UPDATE to emp
DO ALSO
insert into emp_log values (NEW.empno, 'UPDATE');create or replace rule r_del_emp
as on DELETE to emp
DO ALSO
insert into emp_log values (OLD.empno, 'DELETE');postgres=# select * from emp_log;empno | action
-------+--------
(0 rows)postgres=# INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT 0 1
postgres=# INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT 0 1
postgres=# select * from emp_log;empno | action
-------+--------7839 | INSERT7698 | INSERT
(2 rows)postgres=# delete from emp where empno=7698;
DELETE 1
postgres=# update emp set sal=sal*1.1 where empno=7839;
UPDATE 1
postgres=# select * from emp_log;empno | action
-------+--------7839 | INSERT7698 | INSERT7698 | DELETE7839 | UPDATE
(4 rows)

💡 每一個rule只能對應一個事件，即只能為UPDATE，INSERT或DELETE之一加上 WHERE條件。

💡 規則總是在事件發生之前執行。

INSTEAD 選項

create or replace rule r_large_emp
as on INSERT to emp
where NEW.empno between 10000 and 100000
DO INSTEAD
insert into emp_log values (NEW.empno, 'INSTEAD');postgres=# INSERT INTO EMP VALUES (77820,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT 0 0
postgres=# select * from emp_log;empno | action
-------+---------7839 | INSERT7698 | INSERT7698 | UPDATE7839 | UPDATE77820 | INSERT77820 | INSTEAD
(6 rows)

也可以什么都不做：

create or replace rule r_large_emp
as on INSERT to emp
where NEW.empno between 10000 and 100000
DO INSTEAD NOTHING;

管理 PostgreSQL 中的觸發器

如果說rule是事件處理程序，則觸發器是更復雜的事件處理程序。
觸發器可以處理以下的時間：

BEFORE INSERT/UPDATE/DELETE/TRUNCATE
AFTER INSERT/UPDATE/DELETE/TRUNCATE
INSTEAD OF INSERT/UPDATE/DELETE

觸發器語法

這里說明了rule和trigger的區別：

對于兩者均可實現的功能，哪種方式更佳取決于數據庫的使用情況。觸發器會針對每個受影響的行觸發一次。規則會修改查詢或生成額外的查詢。因此，如果一條語句影響多行，則發出一條額外命令的規則可能比針對每一行都調用且必須多次重新確定操作的觸發器更快。然而，觸發器方法在概念上比規則方法簡單得多，新手也更容易上手。

trigger的語法和示例可參見這里。

觸發trigger的事件可以是：

INSERT
UPDATE [ OF column_name [, … ] ]
DELETE
TRUNCATE

💡 如果表中的同一事件上同時存在觸發器和規則，則規則始終在觸發器之前觸發。
💡 如果表中的同一事件上有多個觸發器，則它們按字母順序執行。

插入和更新觸發器

先創建示例表：

CREATE TABLE emp (empno INTEGER PRIMARY KEY,ename TEXT,job TEXT,salary NUMERIC
);CREATE TABLE emp_change_log (id SERIAL PRIMARY KEY,empno INTEGER,column_name TEXT,old_value TEXT,new_value TEXT,changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

創建trigger function：

CREATE OR REPLACE FUNCTION log_emp_change()
RETURNS TRIGGER AS $$
BEGIN-- 檢查 enameIF NEW.ename IS DISTINCT FROM OLD.ename THENINSERT INTO emp_change_log(empno, column_name, old_value, new_value)VALUES (OLD.empno, 'ename', OLD.ename::TEXT, NEW.ename::TEXT);END IF;-- 檢查 jobIF NEW.job IS DISTINCT FROM OLD.job THENINSERT INTO emp_change_log(empno, column_name, old_value, new_value)VALUES (OLD.empno, 'job', OLD.job::TEXT, NEW.job::TEXT);END IF;-- 檢查 salaryIF NEW.salary IS DISTINCT FROM OLD.salary THENINSERT INTO emp_change_log(empno, column_name, old_value, new_value)VALUES (OLD.empno, 'salary', OLD.salary::TEXT, NEW.salary::TEXT);END IF;RETURN NEW;
END;
$$ LANGUAGE plpgsql;

此trigger function可同時用于INSERT和UPDATE。

創建trigger：

CREATE TRIGGER emp_update_trigger
AFTER UPDATE ON emp
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION log_emp_change();CREATE TRIGGER emp_insert_trigger
AFTER INSERT ON emp
FOR EACH ROW
EXECUTE FUNCTION log_emp_change();

測試：

INSERT INTO emp VALUES (1, 'Alice', 'Developer', 5000);
update emp set salary=salary*1.1 where ename = 'Alice';postgres=# select * from emp_change_log;id | empno | column_name | old_value | new_value |         changed_at
----+-------+-------------+-----------+-----------+----------------------------1 |       | ename       |           | Alice     | 2025-06-20 10:22:34.3332652 |       | job         |           | Developer | 2025-06-20 10:22:34.3332653 |       | salary      |           | 5000      | 2025-06-20 10:22:34.3332654 |     1 | salary      | 5000      | 5500.0    | 2025-06-20 10:24:01.273466
(4 rows)

TG_OP 變量

TG_OP指觸發觸發器的操作：INSERT、UPDATE、DELETE 或 TRUNCATE。

實際上還有很多TG_開頭的變量，詳見這里。同時提供了一個例子：

CREATE TABLE emp (empname           text NOT NULL,salary            integer
);CREATE TABLE emp_audit(operation         char(1)   NOT NULL,stamp             timestamp NOT NULL,userid            text      NOT NULL,empname           text      NOT NULL,salary            integer
);CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$BEGIN---- Create a row in emp_audit to reflect the operation performed on emp,-- making use of the special variable TG_OP to work out the operation.--IF (TG_OP = 'DELETE') THENINSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;ELSIF (TG_OP = 'UPDATE') THENINSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;ELSIF (TG_OP = 'INSERT') THENINSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;END IF;RETURN NULL; -- result is ignored since this is an AFTER triggerEND;
$emp_audit$ LANGUAGE plpgsql;CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON empFOR EACH ROW EXECUTE FUNCTION process_emp_audit();

測試：

INSERT INTO emp VALUES ('Alice', 5000);
update emp set salary = 5500 where empname = 'Alice';
delete from emp;postgres=# select * from emp_audit;operation |           stamp            |  userid  | empname | salary
-----------+----------------------------+----------+---------+--------I         | 2025-06-20 10:35:23.803959 | postgres | Alice   |   5000U         | 2025-06-20 10:35:23.806063 | postgres | Alice   |   5500D         | 2025-06-20 10:35:23.807913 | postgres | Alice   |   5500
(3 rows)

事件觸發器

根據文檔：

與附加到單個表并僅捕獲 DML 事件的常規觸發器不同，事件觸發器是特定數據庫的全局觸發器，并且能夠捕獲 DDL 事件。
與常規觸發器一樣，事件觸發器可以使用任何包含事件觸發器支持的過程語言或 C 語言編寫，但不能使用純 SQL 編寫。

完整的DDL時間支持參見這里。還包括數據庫登錄。

事件觸發器示例

這是官網提供的示例，禁止執行任何DDL命令：

CREATE OR REPLACE FUNCTION abort_any_command()RETURNS event_triggerLANGUAGE plpgsqlAS $$
BEGINRAISE EXCEPTION 'command % is disabled', tg_tag;
END;
$$;CREATE EVENT TRIGGER abort_ddl ON ddl_command_startEXECUTE FUNCTION abort_any_command();