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();