PostgreSQL触发器详解：从基础到高级应用-开发者社区

1. 理解PostgreSQL触发器的基础概念

在数据库管理系统中，触发器（Trigger）是一种特殊的存储过程，它会在特定事件发生时自动执行。PostgreSQL作为功能强大的开源关系型数据库，提供了完善的触发器机制。与普通存储过程不同，触发器不由用户直接调用，而是由数据库系统在满足预设条件时自动触发执行。

触发器通常与表相关联，当对表执行INSERT、UPDATE或DELETE操作时触发。PostgreSQL还支持INSTEAD OF触发器，主要用于视图操作。触发器可以在操作之前（BEFORE）或之后（AFTER）执行，也可以替代原操作（INSTEAD OF）。

注意：触发器执行是事务性的，如果触发器执行失败，整个操作（包括触发触发器的语句）都会回滚。

2. PostgreSQL触发器的核心组件

2.1 触发器的基本结构

一个完整的PostgreSQL触发器由以下几个关键部分组成：

触发事件：指定什么操作会触发该触发器（INSERT、UPDATE、DELETE或TRUNCATE）
触发时机：指定触发器在操作之前（BEFORE）还是之后（AFTER）执行
触发条件：可选的WHEN子句，指定触发器执行必须满足的额外条件
触发函数：触发器被触发时执行的实际函数
触发频率：指定触发器是为每个受影响的行执行一次（FOR EACH ROW），还是为每个语句执行一次（FOR EACH STATEMENT）

2.2 触发器函数的特殊变量

在触发器函数中，PostgreSQL提供了几个特殊变量来访问触发上下文：

NEW：包含INSERT/UPDATE操作的新行数据（在DELETE操作中为NULL）
OLD：包含UPDATE/DELETE操作的旧行数据（在INSERT操作中为NULL）
TG_OP：触发操作类型（'INSERT'、'UPDATE'、'DELETE'或'TRUNCATE'）
TG_TABLE_NAME：触发触发器的表名
TG_WHEN：触发时机（'BEFORE'、'AFTER'或'INSTEAD OF'）

3. 创建PostgreSQL触发器的完整流程

3.1 准备工作：创建触发器函数

在创建触发器前，需要先定义一个触发器函数。这个函数必须声明为返回TRIGGER类型，并且不接受任何参数：

CREATE OR REPLACE FUNCTION log_changes() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO audit_log(table_name, operation, new_data) VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW)); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN INSERT INTO audit_log(table_name, operation, old_data, new_data) VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW)); RETURN NEW; ELSIF TG_OP = 'DELETE' THEN INSERT INTO audit_log(table_name, operation, old_data) VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD)); RETURN OLD; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;

3.2 创建触发器

有了触发器函数后，就可以创建触发器了。以下是一个在employees表上创建触发器的示例：

CREATE TRIGGER employees_audit AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW EXECUTE FUNCTION log_changes();

这个触发器会在employees表发生INSERT、UPDATE或DELETE操作后，为每一行调用log_changes()函数。

3.3 带条件的触发器

可以为触发器添加WHEN条件，只有满足条件时才会执行触发器函数：

CREATE TRIGGER check_salary BEFORE UPDATE ON employees FOR EACH ROW WHEN (NEW.salary < 0) EXECUTE FUNCTION reject_negative_salary();

这个触发器只会在尝试将salary设置为负值时触发。

4. PostgreSQL触发器的实际应用场景

4.1 数据审计与变更追踪

触发器最常见的用途是实现数据变更审计。通过创建AFTER触发器，可以记录所有对关键表的修改：

CREATE TABLE audit_log ( id SERIAL PRIMARY KEY, table_name TEXT NOT NULL, operation TEXT NOT NULL, old_data JSONB, new_data JSONB, changed_by TEXT DEFAULT current_user, changed_at TIMESTAMP DEFAULT current_timestamp );

然后为需要审计的表创建触发器，将变更记录到audit_log表中。

4.2 数据完整性与业务规则验证

BEFORE触发器非常适合用于实施复杂的业务规则和数据完整性检查：

CREATE OR REPLACE FUNCTION validate_order() RETURNS TRIGGER AS $$ BEGIN -- 确保订单金额大于0 IF NEW.total_amount <= 0 THEN RAISE EXCEPTION '订单金额必须大于0'; END IF; -- 确保客户有足够的信用额度 PERFORM 1 FROM customers WHERE id = NEW.customer_id AND credit_limit >= NEW.total_amount; IF NOT FOUND THEN RAISE EXCEPTION '客户信用额度不足'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER orders_validation BEFORE INSERT OR UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION validate_order();

4.3 派生数据自动维护

触发器可以自动维护派生数据，保持数据一致性：

CREATE OR REPLACE FUNCTION update_order_stats() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN UPDATE customers SET total_orders = total_orders + 1, total_spent = total_spent + NEW.amount WHERE id = NEW.customer_id; ELSIF TG_OP = 'DELETE' THEN UPDATE customers SET total_orders = total_orders - 1, total_spent = total_spent - OLD.amount WHERE id = OLD.customer_id; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER orders_stats AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW EXECUTE FUNCTION update_order_stats();

5. PostgreSQL触发器的高级特性

5.1 触发器执行顺序

当一个表上有多个触发器时，PostgreSQL允许指定触发器的执行顺序：

CREATE TRIGGER validate_first BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION validate_order(); CREATE TRIGGER log_second AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION log_order_creation();

可以使用WHEN子句进一步控制触发器的执行条件。

5.2 视图上的INSTEAD OF触发器

PostgreSQL支持在视图上创建INSTEAD OF触发器，这使得不可更新的视图变得可更新：

CREATE VIEW customer_orders AS SELECT c.name, o.order_date, o.amount FROM customers c JOIN orders o ON c.id = o.customer_id; CREATE OR REPLACE FUNCTION insert_customer_order() RETURNS TRIGGER AS $$ BEGIN -- 检查客户是否存在 PERFORM 1 FROM customers WHERE name = NEW.name; IF NOT FOUND THEN INSERT INTO customers (name) VALUES (NEW.name); END IF; -- 插入订单 INSERT INTO orders (customer_id, order_date, amount) SELECT id, NEW.order_date, NEW.amount FROM customers WHERE name = NEW.name; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER customer_orders_insert INSTEAD OF INSERT ON customer_orders FOR EACH ROW EXECUTE FUNCTION insert_customer_order();

5.3 事件触发器

除了常规的表触发器，PostgreSQL还支持事件触发器（Event Trigger），用于捕获DDL事件：

CREATE OR REPLACE FUNCTION log_ddl_changes() RETURNS event_trigger AS $$ DECLARE obj record; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP INSERT INTO ddl_log (event, command_tag, object_type, schema_name, object_name) VALUES (tg_event, tg_tag, obj.object_type, obj.schema_name, obj.object_identity); END LOOP; END; $$ LANGUAGE plpgsql; CREATE EVENT TRIGGER log_ddl ON ddl_command_end EXECUTE FUNCTION log_ddl_changes();

6. PostgreSQL触发器的最佳实践与性能考量

6.1 触发器设计原则

保持触发器函数简洁：触发器函数应该尽可能简单，复杂逻辑应该放在普通函数中调用。
避免递归触发器：确保触发器不会导致无限递归调用。
考虑事务影响：触发器执行是事务的一部分，长时间运行的触发器会延长事务时间。
明确文档记录：触发器是隐式执行的，应该在系统文档中明确记录所有触发器的存在和用途。

6.2 性能优化技巧

适当使用FOR EACH STATEMENT：当不需要逐行处理时，使用FOR EACH STATEMENT可以减少触发器调用次数。
合理使用WHEN条件：WHEN条件在触发器被触发后但在函数执行前评估，可以有效减少不必要的函数调用。
避免在触发器中执行耗时操作：如网络请求、复杂计算等，这些操作会显著降低数据库性能。
定期审查触发器：随着业务变化，一些触发器可能不再需要或需要调整，定期审查可以保持系统高效运行。

6.3 常见问题排查

触发器不触发：
- 检查触发器是否已正确创建（\d <表名>）
- 验证触发条件是否满足
- 检查触发器函数是否有语法错误
触发器导致性能问题：
- 使用EXPLAIN ANALYZE分析受影响的查询
- 检查触发器函数中的耗时操作
- 考虑将部分逻辑移到应用层
递归触发器问题：
- 确保触发器不会间接触发自身
- 使用临时表或会话变量来检测递归

7. 触发器与其他PostgreSQL特性的结合使用

7.1 触发器与规则（Rules）

PostgreSQL的规则系统是另一种自动执行操作的方式。与触发器相比，规则在查询重写阶段生效，而触发器在数据操作时生效。在某些情况下，规则可能比触发器更高效：

CREATE RULE log_employee_changes AS ON UPDATE TO employees DO ALSO INSERT INTO employee_changelog VALUES (now(), current_user, OLD.*, NEW.*);

7.2 触发器与通知（NOTIFY）

触发器可以与PostgreSQL的LISTEN/NOTIFY机制结合，实现实时通知：

CREATE OR REPLACE FUNCTION notify_order_change() RETURNS TRIGGER AS $$ BEGIN PERFORM pg_notify('order_changes', json_build_object( 'operation', TG_OP, 'id', COALESCE(NEW.id, OLD.id) )::text); RETURN NEW; END; $$ LANGUAGE plpgsql;

7.3 触发器与扩展

PostgreSQL的扩展如pgAudit提供了更专业的审计功能，但在某些定制化场景中，触发器仍然是更灵活的选择。

8. 触发器管理维护

8.1 查看现有触发器

要查看数据库中定义的触发器，可以使用以下查询：

SELECT tgname AS trigger_name, relname AS table_name, tgtype::bit(8)::int AS trigger_type, tgenabled AS status, pg_get_triggerdef(oid) AS definition FROM pg_trigger JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid WHERE tgisinternal = false;

8.2 修改触发器

PostgreSQL允许修改触发器的某些属性而不必删除重建：

ALTER TRIGGER employees_audit ON employees RENAME TO employees_change_log; ALTER TRIGGER employees_change_log ON employees ENABLE/ DISABLE;

8.3 删除触发器

当不再需要触发器时，可以将其删除：

DROP TRIGGER IF EXISTS employees_audit ON employees;

删除触发器不会删除关联的触发器函数，如果需要，必须单独删除函数：

DROP FUNCTION IF EXISTS log_changes();

9. 实际案例：完整的审计系统实现

下面是一个完整的审计系统实现示例，展示如何利用触发器记录所有关键表的变更：

9.1 创建审计表结构

CREATE SCHEMA audit; CREATE TABLE audit.logged_actions ( event_id bigserial PRIMARY KEY, schema_name text NOT NULL, table_name text NOT NULL, user_name text, action_tstamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp, action TEXT NOT NULL CHECK (action IN ('I','D','U')), original_data jsonb, new_data jsonb, query text ); CREATE INDEX logged_actions_schema_table_idx ON audit.logged_actions (schema_name, table_name); CREATE INDEX logged_actions_action_tstamp_idx ON audit.logged_actions (action_tstamp); CREATE INDEX logged_actions_action_idx ON audit.logged_actions (action);

9.2 创建通用审计触发器函数

CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$ DECLARE audit_row audit.logged_actions; include_values boolean; log_diffs boolean; h_old jsonb; h_new jsonb; excluded_cols text[] = ARRAY[]::text[]; BEGIN IF TG_WHEN <> 'AFTER' THEN RAISE EXCEPTION 'audit.if_modified_func() may only run on an AFTER trigger'; END IF; audit_row = ROW( nextval('audit.logged_actions_event_id_seq'), -- event_id TG_TABLE_SCHEMA::text, -- schema_name TG_TABLE_NAME::text, -- table_name session_user::text, -- user_name current_timestamp, -- action_tstamp substring(TG_OP,1,1), -- action NULL, NULL, -- original_data, new_data current_query() -- query ); IF TG_ARGV[0] IS NOT NULL THEN excluded_cols = TG_ARGV[0]::text[]; END IF; IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN audit_row.original_data = to_jsonb(OLD) - excluded_cols; audit_row.new_data = to_jsonb(NEW) - excluded_cols; INSERT INTO audit.logged_actions VALUES (audit_row.*); ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN audit_row.original_data = to_jsonb(OLD) - excluded_cols; INSERT INTO audit.logged_actions VALUES (audit_row.*); ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN audit_row.new_data = to_jsonb(NEW) - excluded_cols; INSERT INTO audit.logged_actions VALUES (audit_row.*); ELSE RAISE EXCEPTION '[audit.if_modified_func] - Trigger func added as trigger for unhandled op: %', TG_OP; RETURN NULL; END IF; RETURN NULL; END; $body$ LANGUAGE plpgsql SECURITY DEFINER;

9.3 为需要审计的表创建触发器

CREATE TRIGGER customers_audit AFTER INSERT OR UPDATE OR DELETE ON customers FOR EACH ROW EXECUTE FUNCTION audit.if_modified_func(); CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW EXECUTE FUNCTION audit.if_modified_func();

9.4 查询审计日志

SELECT event_id, action_tstamp, user_name, table_name, action, original_data, new_data FROM audit.logged_actions ORDER BY action_tstamp DESC LIMIT 100;

这个完整的审计系统可以记录所有对关键表的修改，包括修改前后的数据、执行操作的用户和时间戳等信息。通过触发器自动捕获这些信息，确保了审计数据的完整性和不可篡改性。

PostgreSQL触发器详解：从基础到高级应用