五月 29, 2024
摘要：在本教程中，您将学习 PostgreSQL 事件触发器，以及如何使用CREATE EVENT TRIGGER语句定义一个新的事件触发器。
目录
PostgreSQL 事件触发器简介
一个常规触发器，是在一个关联表上发生INSERT、UPDATE、DELETE或TRUNCATE事件时触发的。
若要自动响应与数据定义语言（DDL）语句相关的事件，可以使用事件触发器。
事件触发器是在每当数据库中发生关联事件时触发的触发器。
PostgreSQL 支持以下事件：
ddl_command_startddl_command_endtable_rewritesql_drop
ddl_command_start事件，在 PostgreSQL 执行CREATE、ALTER、DROP、GRANT、REVOKE、SECURITY LABEL和COMMENT语句之前触发。有关事件触发器支持的完整命令，请阅读事件触发器触发矩阵的更多内容。
请注意，对于集群级共享对象（如数据库、表空间和角色），不会发生ddl_command_start事件。
ddl_command_end事件发生在执行以上 SQL 语句之后。
每当您删除一个数据库对象时，会在ddl_command_end事件之前发生sql_drop事件。
table_rewrite事件发生在使用ALTER TABLE或ALTER TYPE语句重写一个表之前。
要创建一个事件触发器，可按照下列步骤操作：
首先，定义一个函数，该函数将在事件触发器触发时执行：
CREATE OR REPLACE FUNCTION event_trigger_function_name()
RETURNS EVENT_TRIGGER
AS
$$
BEGIN
-- trigger logic
-- ...
-- no RETURN statement
END;
$$;
事件触发器函数返回EVENT_TRIGGER而不是TRIGGER。此外，它不像常规触发器函数那样，有任何的RETURN语句。
第二步，使用CREATE EVENT TRIGGER语句，创建一个事件触发器：
CREATE EVENT TRIGGER trigger_name
ON event
EXECUTE FUNCTION event_trigger_function_name()
PostgreSQL 事件触发器示例
首先，创建一个表，名为audits，用来存储命令的审计日志：
CREATE TABLE audits (
id SERIAL PRIMARY KEY,
username VARCHAR(100) NOT NULL,
event VARCHAR(50) NOT NULL,
command TEXT NOT NULL,
executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
audits表将记录执行命令时的用户名、事件、命令和时间戳。
第二步，定义一个事件触发器函数，在每当相关事件发生时执行：
CREATE OR REPLACE FUNCTION audit_command()
RETURNS EVENT_TRIGGER
AS $$
BEGIN
INSERT INTO audits (username, event , command)
VALUES (session_user, TG_EVENT, TG_TAG );
END;
$$ LANGUAGE plpgsql;
audit_command()函数会将审计记录插入到audits表中。
第三步，创建一个事件触发器，以将函数与 DDL 命令关联起来：
CREATE EVENT TRIGGER audit_ddl_commands
ON ddl_command_end
EXECUTE FUNCTION audit_command();
第四步，执行一个CREATE TABLE命令：
CREATE TABLE regions(
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
第五步，从audits表中检索数据：
SELECT * FROM audits;
输出：
id | username | event | command | executed_at
----+----------+-----------------+--------------+----------------------------
1 | postgres | ddl_command_end | CREATE TABLE | 2024-03-29 12:12:38.773734
(1 row)
常规触发器与事件触发器
下表比较了常规触发器和事件触发器：
| 特性 | 常规触发器 | 事件触发器 |
|---|---|---|
| 触发级别 | 表级触发器，与特定表关联，在INSERT、UPDATE、DELETE或TRUNCATE语句上触发。 |
数据库级触发器，用于响应CREATE、ALTER、DROP等 DDL 语句。 |
| 执行时间 | 可在BEFORE、AFTER或INSTEAD OF DML操作时触发 |
在一些事件发生时触发，包括ddl_command_start、ddl_command_end、table_rewrite、sql_drop |
| 范围 | 可以定义在行级或语句级 | 在数据库级别执行 |
| 数据访问权限 | 有权访问正在修改的数据 | 有权访问元数据 |
| 使用场景 | 将更改记录到一个特定表、更新相关表以及检查业务规则。 | 审计 DDL 命令，和监控用户活动。 |
总结
- 事件触发器是在发生与 DDL 语句相关的事件时触发的触发器。
- 使用
CREATE EVENT TRIGGER语句定义新的事件触发器。